php - MySQL Sum、Count 还是 Join？

Question

我有以下 MySQL 查询:

SELECT 
Country, City, 
COUNT(City) As Total, 
SUM(completion_status IN ('Started', 'Complete')) AS Total_Resp
FROM trespondent 
WHERE completion_status <> 'New' 
GROUP BY Country, City 
ORDER BY Counntry, City

这带回了我所期望的，这是一个城市列表，每个城市都有计数，完全没问题

Country     City     Total     Total_Resp
UK          London   150       23
UK          Leeds    150       83
France      Paris    235       99
France      Lyon     421       222

我想做的是把整个国家的总数也带回来，所以英国和法国作为一个整体看起来像这样:

Country     City     Total_Country   Total_city   Total_Resp
UK          London   300                150       23
UK          Leeds    300                150       83
France      Paris    656                235       99
France      Lyon     656                421       222

现在，我尝试简单地添加到 SQL 语句中

COUNT(Country) As Total_Country
SUM(Country) As Total_Country

但两者都带回与城市相同的计数，我现在认为如果不使用 JOIN 是不可能的。任何人都可以建议吗？

提前致谢。

Answer 1

您可以为此使用相关子查询:

SELECT t1.Country, t1.City, 
       (SELECT COUNT(t2.Country)
        FROM trespondent AS t2
        WHERE t2.Country = t1.Country) AS Total_Country,
        COUNT(t1.City) As Total_city, 
       SUM(t1.completion_status IN ('Started', 'Complete')) AS Total_Resp
FROM trespondent AS t1
WHERE t1.completion_status <> 'New' 
GROUP BY t1.Country, t1.City 
ORDER BY t1.Country, t1.City