php - 对一些数据进行分组

Question

我有一个 SQL 查询:

SELECT COUNT(id) AS number_plays, id
FROM table
WHERE date_play >= '2017-08-27 00:00:00' AND date_play <= '2017-08-27 23:59:59'
GROUP BY id, date_play
HAVING number_plays > 1
ORDER BY number_plays DESC

结果是这样的:

number_plays                id
20                          15
40                          15
32                          18
20                          15

是否可以按播放次数对结果进行分组？以这样的方式结束:

iterations                 number_plays          id
2                          20                    15
1                          40                    15
1                          32                    18

我能得到一些帮助吗？提前致谢。

Answer 1

两件事 - 首先，如果您在 GROUP BY 子句中指定了一个列，请尝试在 SELECT 中也指定它。只有 MySQL 允许这样做，我发现它是错误的并且会引发错误/输出错误的数据，你不会理解为什么。

其次，只需用另一个查询包装您的查询，并将 number_plays 添加到 GROUP BY 子句:

SELECT count(*) as iterations,t.number_plays,t.id
FROM(
    SELECT  id,date_play,COUNT(id) AS number_plays,
    FROM table
    WHERE date_play >= '2017-08-27 00:00:00' 
      AND date_play <= '2017-08-27 23:59:59'
    GROUP BY id, date_play
    HAVING number_plays > 1
    ORDER BY number_plays DESC) t
GROUP BY t.number_plays,t.id