mysql 查询问题,请问我这sql该怎么写

就是我想不跟日期，只根据时间做查询，将一天分六段的查询，思路大概是这样的，但sql并不允许我这样去查
slelect ( select count(id) from Tabie where hour(occurrence_time) between 0 and 3) as ones
( SELECT COUNT(id) FROM Tabie WHERE HOUR(occurrence_time) BETWEEN 4 AND 7) as twos,
( SELECT COUNT(id) FROM Tabie WHERE HOUR(occurrence_time) BETWEEN 8 AND 11) as threes,
( SELECT COUNT(id) FROM Tabie WHERE HOUR(occurrence_time) BETWEEN 12 AND 15)as fours,
( SELECT COUNT(id) FROM Tabie WHERE HOUR(occurrence_time) BETWEEN 16 AND 19)as fives,
( SELECT COUNT(id) FROM Tabie WHERE HOUR(occurrence_time) BETWEEN 20 AND 23)as sixs
from Table
where 1=1
and occurrence_time between '2021-01-01 00:00:00' and '2021-12-01 00:00:00';

提示什么报错

你sql写错了，子查询不是这么用的。

这样写试试

select count(id) as count,  'ones' as section
from Table 
where 1=1
and occurrence_time between '2021-01-01 00:00:00' and '2021-12-01 00:00:00'
and HOUR(t,occurrence_time)  between 0 and 3
unoin all
select count(id) as count,  'twos' as section
from Table 
where 1=1
and occurrence_time between '2021-01-01 00:00:00' and '2021-12-01 00:00:00'
and HOUR(t,occurrence_time) BETWEEN 4 AND 7
unoin all
select count(id) as count,  'threes' as section
from Table 
where 1=1
and occurrence_time between '2021-01-01 00:00:00' and '2021-12-01 00:00:00'
and HOUR(t,occurrence_time)  BETWEEN 8 AND 11
unoin all
select count(id) as count,  'fours' as section
from Table 
where 1=1
and occurrence_time between '2021-01-01 00:00:00' and '2021-12-01 00:00:00'
and HOUR(t,occurrence_time) BETWEEN 12 AND 15
unoin all
select count(id) as count,  'fives' as section
from Table 
where 1=1
and occurrence_time between '2021-01-01 00:00:00' and '2021-12-01 00:00:00'
and HOUR(t,occurrence_time) BETWEEN 16 AND 19
unoin all
select count(id) as count,  'sixs' as section
from Table 
where 1=1
and occurrence_time between '2021-01-01 00:00:00' and '2021-12-01 00:00:00'
and HOUR(t,occurrence_time) BETWEEN 20 AND 23

可以使用CASE when 语句试试

CASE WHEN  HOUR(t,occurrence_time)  between 0 and 3 THEN 1
         WHEN HOUR(t,occurrence_time) BETWEEN 4 AND 7 THEN 2
          WHEN HOUR(t,occurrence_time) BETWEEN 8 AND 11 THEN 3
          WHEN HOUR(t,occurrence_time) BETWEEN 12 AND 15 THEN 4
          WHEN HOUR(t,occurrence_time) BETWEEN 16 AND 19 THEN 5
          ELSE 6
 end as type


SELECT COUNT(id),CASE WHEN  HOUR(occurrence_time)  between 0 and 3 THEN 1
         WHEN HOUR(occurrence_time) BETWEEN 4 AND 7 THEN 2
          WHEN HOUR(occurrence_time) BETWEEN 8 AND 11 THEN 3
          WHEN HOUR(occurrence_time) BETWEEN 12 AND 15 THEN 4
          WHEN HOUR(occurrence_time) BETWEEN 16 AND 19 THEN 5
          ELSE 6
 end as type FROM `Tabie` GROUP BY CASE WHEN  HOUR(occurrence_time)  between 0 and 3 THEN 1
         WHEN HOUR(occurrence_time) BETWEEN 4 AND 7 THEN 2
          WHEN HOUR(occurrence_time) BETWEEN 8 AND 11 THEN 3
          WHEN HOUR(occurrence_time) BETWEEN 12 AND 15 THEN 4
          WHEN HOUR(occurrence_time) BETWEEN 16 AND 19 THEN 5
          ELSE 6
 end

上面结果类似：

img