SQL 同一天不同時刻數據分組查詢技巧

如何將同一天的多條記錄合併查詢

業務場景：表中存儲精確到分鐘的時間戳，需按「天」維度聚合展示。例如：

2015/05/01 02:00
2015/05/01 05:30
2015/05/01 18:00
2015/05/03 13:00
...

核心思路：日期截取 + GROUP BY

不同數據庫的日期截取函數不同，但邏輯一致：

MySQL

SELECT DATE(create_time) AS day, GROUP_CONCAT(TIME(create_time)) AS times
FROM your_table
GROUP BY DATE(create_time);

SQL Server

SELECT CAST(create_time AS DATE) AS day,
       STUFF((SELECT ', ' + CAST(TIME(create_time) AS VARCHAR(5))
              FROM your_table t2
              WHERE CAST(t2.create_time AS DATE) = CAST(t1.create_time AS DATE)
              FOR XML PATH('')), 1, 2, '') AS times
FROM your_table t1
GROUP BY CAST(create_time AS DATE);

Oracle

SELECT TRUNC(create_time) AS day,
       LISTAGG(TO_CHAR(create_time, 'HH24:MI'), ', ') WITHIN GROUP (ORDER BY create_time) AS times
FROM your_table
GROUP BY TRUNC(create_time);

進階：直接聚合統計

若只需每天的記錄數、最早/最晚時間：

SELECT DATE(create_time) AS day,
       COUNT(*) AS cnt,
       MIN(create_time) AS first_time,
       MAX(create_time) AS last_time
FROM your_table
GROUP BY DATE(create_time);

建議在時間欄位上建立索引，大幅提升分組查詢性能。