我用3个查询访问我的网站上的数据，第一个查询获得网站上可用的交易的完整列表:
$listingquery = "SELECT * FROM table WHERE city = '$city' AND approved = '1'
AND enddate > NOW() ORDER BY id DESC";
下一个查询获取可用交易提供者的计数/列表:
$providerquery = "SELECT programname, category, COUNT(name) FROM table WHERE
city = '$city' AND approved='1' AND enddate > NOW() GROUP BY provider";
和第三个查询从该城市的可用交易提供商获取类别的计数/列表:
$categoryquery = "SELECT programname, category, COUNT(name) FROM table WHERE
city = '$city' AND approved='1' AND enddate > NOW() GROUP BY category";
是否有一种方法将所有这些合并到一个查询中?
提前感谢!
你可能会得到一些无损信息，但你可以试试这个:
SELECT * FROM table a
LEFT JOIN (
SELECT programname, category, COUNT(name)
FROM table
WHERE city = '$city' AND approved='1' AND enddate > NOW() GROUP BY provider
) b ON a.provider = b.provider
LEFT JOIN (
SELECT programname, category, COUNT(name)
FROM table
WHERE city = '$city' AND approved='1' AND enddate > NOW() GROUP BY category
) c ON a.category = b.category
WHERE a.city = '$city' AND a.approved = '1'
AND a.enddate > NOW()
ORDER BY a.id DESC;
用一个Where子句(稍微)更新:
SELECT * FROM table a
LEFT JOIN (
SELECT programname, category, COUNT(name)
FROM table
WHERE enddate > NOW() GROUP BY provider
) b ON a.provider = b.provider
LEFT JOIN (
SELECT programname, category, COUNT(name)
FROM table
WHERE enddate > NOW() GROUP BY category
) c ON a.category = b.category
WHERE a.city = '$city' AND a.approved = '1'
AND a.enddate > NOW()
AND b.city = a.city AND c.city = a.city
AND b.approved = a.approved AND c.approved = a.approved
ORDER BY a.id DESC;
我怀疑所有这些在单个查询中是可能的/可行的，但是无论如何你都不应该在MySQL中使用子查询，它会大大降低性能。