我有一个SQL SELECT查询，也使用GROUP BY， 我想统计GROUP BY子句过滤结果集后的所有记录。
有什么方法可以直接用SQL做到这一点吗?例如，如果我有用户表，想要选择不同的城镇和用户总数:
SELECT `town`, COUNT(*)
FROM `user`
GROUP BY `town`;
我想有一个列的所有城镇和另一个与用户的数量在所有行。
一个有3个镇和58个用户的结果的例子是:
| Town | Count |
|---|---|
| Copenhagen | 58 |
| New York | 58 |
| Athens | 58 |
当前回答
10个未删除的答案;大多数不做用户要求的事情。大多数回答错误地理解了这个问题，认为每个城镇有58个用户，而不是总共有58个。即使是少数正确的，也不是最优的。
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
SELECT province, total_cities
FROM ( SELECT DISTINCT province FROM canada ) AS provinces
CROSS JOIN ( SELECT COUNT(*) total_cities FROM canada ) AS tot;
+---------------------------+--------------+
| province | total_cities |
+---------------------------+--------------+
| Alberta | 5484 |
| British Columbia | 5484 |
| Manitoba | 5484 |
| New Brunswick | 5484 |
| Newfoundland and Labrador | 5484 |
| Northwest Territories | 5484 |
| Nova Scotia | 5484 |
| Nunavut | 5484 |
| Ontario | 5484 |
| Prince Edward Island | 5484 |
| Quebec | 5484 |
| Saskatchewan | 5484 |
| Yukon | 5484 |
+---------------------------+--------------+
13 rows in set (0.01 sec)
显示会话状态，例如'Handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 4 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 3 |
| Handler_read_key | 16 |
| Handler_read_last | 1 |
| Handler_read_next | 5484 | -- One table scan to get COUNT(*)
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 15 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 14 | -- leapfrog through index to find provinces
+----------------------------+-------+
在OP的背景下:
SELECT town, total_users
FROM ( SELECT DISTINCT town FROM canada ) AS towns
CROSS JOIN ( SELECT COUNT(*) total_users FROM canada ) AS tot;
因为只有一行来自tot，所以CROSS JOIN并不像其他情况那样庞大。
通常的模式是COUNT(*)而不是COUNT(town)。后者意味着检查town是否为非空，这在此上下文中是不必要的。
其他回答
这将做你想做的(城镇列表，每个城镇的用户数量):
SELECT `town`, COUNT(`town`)
FROM `user`
GROUP BY `town`;
在使用GROUP BY语句时，可以使用大多数聚合函数 (COUNT, MAX, COUNT DISTINCT等)
更新: 你可以为用户数量声明一个变量并保存结果，然后选择变量的值:
DECLARE @numOfUsers INT
SET @numOfUsers = SELECT COUNT(*) FROM `user`;
SELECT DISTINCT `town`, @numOfUsers FROM `user`;
如果你想按计数排序(听起来很简单，但我无法在如何做到这一点的堆栈上找到答案)，你可以这样做:
SELECT town, count(town) as total FROM user
GROUP BY town ORDER BY total DESC
10个未删除的答案;大多数不做用户要求的事情。大多数回答错误地理解了这个问题，认为每个城镇有58个用户，而不是总共有58个。即使是少数正确的，也不是最优的。
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
SELECT province, total_cities
FROM ( SELECT DISTINCT province FROM canada ) AS provinces
CROSS JOIN ( SELECT COUNT(*) total_cities FROM canada ) AS tot;
+---------------------------+--------------+
| province | total_cities |
+---------------------------+--------------+
| Alberta | 5484 |
| British Columbia | 5484 |
| Manitoba | 5484 |
| New Brunswick | 5484 |
| Newfoundland and Labrador | 5484 |
| Northwest Territories | 5484 |
| Nova Scotia | 5484 |
| Nunavut | 5484 |
| Ontario | 5484 |
| Prince Edward Island | 5484 |
| Quebec | 5484 |
| Saskatchewan | 5484 |
| Yukon | 5484 |
+---------------------------+--------------+
13 rows in set (0.01 sec)
显示会话状态，例如'Handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 4 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 3 |
| Handler_read_key | 16 |
| Handler_read_last | 1 |
| Handler_read_next | 5484 | -- One table scan to get COUNT(*)
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 15 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 14 | -- leapfrog through index to find provinces
+----------------------------+-------+
在OP的背景下:
SELECT town, total_users
FROM ( SELECT DISTINCT town FROM canada ) AS towns
CROSS JOIN ( SELECT COUNT(*) total_users FROM canada ) AS tot;
因为只有一行来自tot，所以CROSS JOIN并不像其他情况那样庞大。
通常的模式是COUNT(*)而不是COUNT(town)。后者意味着检查town是否为非空，这在此上下文中是不必要的。
如果你想选择城镇和总用户数量，你可以使用下面的查询:
SELECT Town, (SELECT Count(*) FROM User) `Count` FROM user GROUP BY Town;
如果你想使用Select All Query With Count选项，试试这个…
select a.*, (Select count(b.name) from table_name as b where Condition) as totCount from table_name as a where where Condition
