Pengiraan setiap jam berdasarkan data masa mula dan masa tamat

Question

Dalam jadual, data adalah dalam format cap masa tetapi saya berkongsinya dalam format masa(mula_pada) dan masa(akhir_pada). Struktur jadual: id,start_at,end_at1,03:00:00,06:00:002,02:00:00,05:00:003,01:00:00,08:00:004,08:00:00 ,13:00:005,09:00:00,21:00:006,13:00:00,16:00:006,15:00:00,19:

P粉432930081 · Answer

atau

WITH RECURSIVE
cte AS (
    SELECT 0 `hour`
    UNION ALL
    SELECT `hour` + 1 FROM cte WHERE `hour` < 23
)
SELECT cte.`hour`, COUNT(test.id) `count`
FROM cte 
LEFT JOIN test ON cte.`hour` >= HOUR(test.start_at)
              AND cte.`hour` < HOUR(test.end_at)
GROUP BY 1
ORDER BY 1;

atau

WITH RECURSIVE
cte AS (
    SELECT CAST('00:00:00' AS TIME) `hour`
    UNION ALL
    SELECT `hour` + INTERVAL 1 HOUR FROM cte WHERE `hour` < '23:00:00'
)
SELECT cte.`hour`, COUNT(test.id) `count`
FROM cte 
LEFT JOIN test ON cte.`hour` >= test.start_at 
              AND cte.`hour` < test.end_at 
GROUP BY 1
ORDER BY 1;

Pertanyaan pertama mengembalikan lajur jam dalam format masa, manakala pertanyaan kedua mengembalikan lajur ini dalam bentuk angka. Pilih varian yang selamat untuk anda.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5a77b6e3158be06c7a551cb7e64673de