mysql - 这样的需求能不能用一条SQL实现？-PHP中文网问答

mysql - 这样的需求能不能用一条SQL实现？

高洛峰 2017-04-17 11:16:06

[MySQL讨论组]

326

现在想要这样的效果：

name  monthly_count   total_count
a     10              20
b     15              25

name 的 monthly_count 取得方法为：

    select name, count(*) as monthly_count from table_name where updated_at = yyyymm

total_count 的取得条件是：

    select count(*) as total_count from table_name

即不加按月统计。

用一条SQL语句能不能实现呢？

高洛峰

拥有18年软件开发和IT教学经验。曾任多家上市公司技术总监、架构师、项目经理、高级软件工程师等职务。网络人气名人讲师，...

全部回复(1)

大家讲道理2017-04-17 11:18:06 1楼

SELECT total_count.name, total_count.cnt, month_count.cnt FROM
(SELECT name, COUNT(*) AS cnt FROM table_name GROUP BY name) AS total_count 
LEFT JOIN
(SELECT name, COUNT(*) AS cnt FROM table_name WHERE updated_at = yyyymm GROUP BY name) AS month_count
ON total_count.name = month_count.name

赞 +0

添加回复