我有一个MySQL表,数据为:-
country | city
---------------
italy | milan
italy | rome
italy | rome
ireland | cork
uk | london
ireland | cork
我想查询此信息并按国家和城市分组,并具有城市和国家的计数,如下所示:-
country | city | city_count | country_count
---------------------------------------------
ireland | cork | 2 | 2
italy | milan | 1 | 3
italy | rome | 2 | 3
uk | london | 1 | 1
我可以:-
SELECT country, city, count(city) as city_count
FROM jobs
GROUP BY country, city
这给了我:-
country | city | city_count
-----------------------------
ireland | cork | 2
italy | milan | 1
italy | rome | 2
uk | london | 1
是否也有指向country_count的指针?
解决方法:
您可以使用相关的子查询:
SELECT country, city, count(city) as city_count,
(SELECT count(*)
FROM jobs AS j2
WHERE j1.country = j2.country) AS country_count
FROM jobs AS j1
GROUP BY country, city
标签:sql,mysql,group-by
来源: https://codeday.me/bug/20191119/2037743.html