我有一张桌子：
date, number, flag1, flag2, flag3
2015, 10, 1, NULL, NULL
2015, 10, 1, NULL, NULL
2015, 10, 0, NULL, NULL
2015, 11, 1, NULL, NULL
2015, 11, NULL, 1, NULL
2015, 11, NULL, 0, NULL
2015, 12, NULL, NULL, 0
2016, 10, 1, NULL, NULL
2016, 11, 0, NULL, NULL
2016, 13, NULL, 1, NULL
2016, 13, NULL, NULL, 1
2016, 13, NULL, NULL, 1
2016, 13, NULL, NULL, 1
（NULL = 0）
我需要按日期对数据进行分组：
date, flag1, flag2, flag3
2015, 2, 1 0
2016, 1, 1, 1
详细说明：
对于每个日期，必须对flag1，flag2，flag3（其flag = 1）和相同的数字进行计数
例如对于flag1：
number = 10, flag1 = 1
number = 10, flag1 = 0
number = 10, flag1 = 1
number = 10, flag1 = 1
将计数= 1
number = 10, flag1 = 1
number = 10, flag1 = 0
number = 11, flag1 = 1
number = 11, flag1 = 1
将计数= 2
number = 10, flag1 = 0
number = 10, flag1 = 0
number = 11, flag1 = 1
number = 11, flag1 = 1
将计数= 1
编写了警告的sql代码，但是很困难，很慢等
SELECT
date,
SUM(count1) AS count1,
SUM(count2) AS count2,
SUM(count3) AS count3
FROM
(
SELECT
date,
IF(SUM(flag1) <> 0, 1, 0) AS count1,
IF(SUM(flag2) <> 0, 1, 0) AS count2,
IF(SUM(flag3) <> 0, 1, 0) AS count3
FROM
table
-- WHERE
GROUP BY
number
) AS tmp
GROUP BY
date
ORDER BY
date;
提示是否简化和加快代码可以
简化版
SELECT
date,
SUM(count1) AS count1,
SUM(count2) AS count2,
SUM(count3) AS count3
FROM
(
SELECT
date,
COUNT(DISTINCT(flag1)) AS count1,
COUNT(DISTINCT(flag2)) AS count2,
COUNT(DISTINCT(flag3)) AS count3
FROM
table
-- WHERE
GROUP BY
object_id
) AS tmp
GROUP BY
date
ORDER BY
date;
最佳答案
我建议您将表重组为：
date, number, flag_no, flag_value
2015, 10, 1, 1
2015, 10, 1, 1
2015, 10, 1, 0
2015, 11, 1, 1
2016, 10, 1, 1
2016, 11, 1, 0
2015, 11, 2, 1
2015, 11, 2, 0
2016, 13, 2, 1
2015, 12, 3, 0
2016, 13, 3, 1
2016, 13, 3, 1
2016, 13, 3, 1
关于mysql - mysql:简化请求-按多个字段分组，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40108525/