一次查询中的多个计数

Question

这个查询不起作用。有人可以帮我吗？

$query = "SELECT 
    COUNT(WHERE Name='george') AS george_total,
    COUNT(WHERE Name='michael') AS michael_total,
    COUNT(WHERE Name='mike') AS mike_total 
FROM users WHERE Banned IS NOT '1' AND Active='yes' AND Logins>1 AND Registered_to_forum='1'";

$row=mysql_fetch_array($result);
echo "
    We found $row['george_total'] people with the name 'George' in our database,
    $row['michael_total'] with the name Michael
    and $row['mike_total'] with the name Mike.
";

Answer 1

您可以使用 CASE 语句以及 COUNT 或 SUM。

COUNT 版本如下。 COUNT 仅计算 NOT NULL 值，因此您可以使用任何非空列或常量而不是 1。

SELECT 
    COUNT(CASE WHEN Name='george' THEN 1 END) AS george_total,
    COUNT(CASE WHEN Name='michael' THEN 1 END) AS michael_total,
    COUNT(CASE WHEN Name='mike' THEN 1 END) AS mike_total /*.... rest of query*/

SUM 版本为

SELECT 
    SUM(CASE WHEN Name='george' THEN 1 ELSE 0 END) AS george_total,
    SUM(CASE WHEN Name='michael' THEN 1 ELSE 0 END) AS michael_total,
    SUM(CASE WHEN Name='mike' THEN 1 ELSE 0 END) AS mike_total /*.... rest 
                                                                   of query*/