Pengiraan setiap jam berdasarkan data masa mula dan masa tamat
P粉283559033
2023-08-30 15:53:41
<p>Dalam jadual, data dalam format cap masa tetapi saya berkongsinya dalam format masa(mula_pada) dan masa(akhir_pada). </p>
<p>Struktur jadual:</p>
<pre class="brush:php;toolbar:false;">id, start_at, end_at
1, 03:00:00, 06:00:00
2, 02:00:00, 05:00:00
3, 01:00:00, 08:00:00
4, 08:00:00, 13:00:00
5, 09:00:00, 21:00:00
6, 13:00:00, 16:00:00
6, 15:00:00, 19:00:00</pra>
<p>Untuk hasilnya, kita perlu mengira bilangan id aktif antara masa mula_pada dan tamat. </p>
<pre class="brush:php;toolbar:false;">jam, kira
0, 0
1, 1
dua puluh dua
3, 3
4, 3
5, 2
6, 1
7, 1
8,1
9, 2
10, 2
11, 2
12, 2
13, 3
14, 2
15, 3
16, 2
17, 2
18, 2
19,1
20,1
21,0
22,0
23, 0</pra></p>
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
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