时间段分组的SQL语句请教

select case when convert(char(2),lTime,108)>=0 and convert(char(2),lTime,108)<=1

then '00:00-01:00'

when convert(char(2),lTime,108)>=1 and convert(char(2),lTime,108)<=2

then '01:00-02:00'

when convert(char(2),lTime,108)>=2 and convert(char(2),lTime,108)<=3

then '02:00-03:00'

when convert(char(2),lTime,108)>=3 and convert(char(2),lTime,108)<=4

then '03:00-04:00'

when convert(char(2),lTime,108)>=4 and convert(char(2),lTime,108)<=5

then '04:00-05:00'

when convert(char(2),lTime,108)>=5 and convert(char(2),lTime,108)<=6

then '05:00-06:00'

when convert(char(2),lTime,108)>=6 and convert(char(2),lTime,108)<=7

then '06:00-07:00'

when convert(char(2),lTime,108)>=7 and convert(char(2),lTime,108)<=8

then '07:00-08:00'

when convert(char(2),lTime,108)>=8 and convert(char(2),lTime,108)<=9

then '08:00-09:00'

when convert(char(2),lTime,108)>=9 and convert(char(2),lTime,108)<=10

then '09:00-10:00'

when convert(char(2),lTime,108)>=10 and convert(char(2),lTime,108)<=11

then '10:00-11:00'

when convert(char(2),lTime,108)>=11 and convert(char(2),lTime,108)<=12

then '11:00-12:00'

when convert(char(2),lTime,108)>=12 and convert(char(2),lTime,108)<=13

then '12:00-13:00'

when convert(char(2),lTime,108)>=13 and convert(char(2),lTime,108)<=14

then '13:00-14:00'

when convert(char(2),lTime,108)>=14 and convert(char(2),lTime,108)<=15

then '14:00-15:00'

when convert(char(2),lTime,108)>=15 and convert(char(2),lTime,108)<=16

then '15:00-16:00'

when convert(char(2),lTime,108)>=16 and convert(char(2),lTime,108)<=17

then '16:00-17:00'

when convert(char(2),lTime,108)>=17 and convert(char(2),lTime,108)<=18

then '17:00-18:00'

when convert(char(2),lTime,108)>=18 and convert(char(2),lTime,108)<=19

then '18:00-19:00'

when convert(char(2),lTime,108)>=19 and convert(char(2),lTime,108)<=20

then '19:00-20:00'

when convert(char(2),lTime,108)>=20 and convert(char(2),lTime,108)<=21

then '20:00-21:00'

when convert(char(2),lTime,108)>=21 and convert(char(2),lTime,108)<=22

then '21:00-22:00'

when convert(char(2),lTime,108)>=22 and convert(char(2),lTime,108)<=23

then '22:00-23:00'

when convert(char(2),lTime,108)>=23 and convert(char(2),lTime,108)<=24

then '23:00-00:00'

end as 时间段,sum(lNum)合计

from listtable group by

case when convert(char(2),lTime,108)>=0 and convert(char(2),lTime,108)<=1

then '00:00-01:00'

when convert(char(2),lTime,108)>=1 and convert(char(2),lTime,108)<=2

then '01:00-02:00'

when convert(char(2),lTime,108)>=2 and convert(char(2),lTime,108)<=3

then '02:00-03:00'

when convert(char(2),lTime,108)>=3 and convert(char(2),lTime,108)<=4

then '03:00-04:00'

when convert(char(2),lTime,108)>=4 and convert(char(2),lTime,108)<=5

then '04:00-05:00'

when convert(char(2),lTime,108)>=5 and convert(char(2),lTime,108)<=6

then '05:00-06:00'

when convert(char(2),lTime,108)>=6 and convert(char(2),lTime,108)<=7

then '06:00-07:00'

when convert(char(2),lTime,108)>=7 and convert(char(2),lTime,108)<=8

then '07:00-08:00'

when convert(char(2),lTime,108)>=8 and convert(char(2),lTime,108)<=9

then '08:00-09:00'

when convert(char(2),lTime,108)>=9 and convert(char(2),lTime,108)<=10

then '09:00-10:00'

when convert(char(2),lTime,108)>=10 and convert(char(2),lTime,108)<=11

then '10:00-11:00'

when convert(char(2),lTime,108)>=11 and convert(char(2),lTime,108)<=12

then '11:00-12:00'

when convert(char(2),lTime,108)>=12 and convert(char(2),lTime,108)<=13

then '12:00-13:00'

when convert(char(2),lTime,108)>=13 and convert(char(2),lTime,108)<=14

then '13:00-14:00'

when convert(char(2),lTime,108)>=14 and convert(char(2),lTime,108)<=15

then '14:00-15:00'

when convert(char(2),lTime,108)>=15 and convert(char(2),lTime,108)<=16

then '15:00-16:00'

when convert(char(2),lTime,108)>=16 and convert(char(2),lTime,108)<=17

then '16:00-17:00'

when convert(char(2),lTime,108)>=17 and convert(char(2),lTime,108)<=18

then '17:00-18:00'

when convert(char(2),lTime,108)>=18 and convert(char(2),lTime,108)<=19

then '18:00-19:00'

when convert(char(2),lTime,108)>=19 and convert(char(2),lTime,108)<=20

then '19:00-20:00'

when convert(char(2),lTime,108)>=20 and convert(char(2),lTime,108)<=21

then '20:00-21:00'

when convert(char(2),lTime,108)>=21 and convert(char(2),lTime,108)<=22

then '21:00-22:00'

when convert(char(2),lTime,108)>=22 and convert(char(2),lTime,108)<=23

then '22:00-23:00'

when convert(char(2),lTime,108)>=23 and convert(char(2),lTime,108)<=24

then '23:00-00:00' end

--表是ListTable 时间字段lTime,数量字段lNum

有点点麻烦，需要先构建一个关于时间段的虚表

先找到你订单的最早的时间，然后找一个行数足够多的表

假设你的表名叫s，也就是你罗列出数据这个表

(select row_number() over (order by id)-1 rn from sheet2)--sheet2替换成你行数足够多的表,还有order by id里的id你随便替换你表里的一个字段

select dateadd(hour,10+24trn,cast('2013/3/1' as datetime)) begin_time,--2013/3/1换成你表里的最小时间

dateadd(hour,10+24(trn+1),cast('2013/3/1' as datetime)) end_time,

count() 订单数量 

from t,s--这个s替换成你自己的表名

where cast(s时间)>=dateadd(hour,10+24trn,cast('2013/3/1' as datetime))

and cast(s时间)<dateadd(hour,10+24(trn+1),cast('2013/3/1' as datetime))

对，sql对DateTime类型数据 *** 作时都会精确到毫秒，而用了CONVERT(varchar, in_date, 120)后会转换成yyyy-mm-dd hh:mi:ss(24小时制)格式

提供一个思路，实时上我们也一般这么干

1将datetime转类型为字符串 ，按照年月日时分秒

2,截取字符串，取年月日时并分组

3完成分组后，转换回来

在ORACLE里，是类似如下的代码

select to_date(substr( to_char(datetime,'yyyy-mm-dd-hh-mi-ss'),1,13),'yyyy-mm-dd-hh')from table t

group by substr( to_char(datetime,'yyyy-mm-dd-hh-mi-ss'),1,13)

以上就是关于时间段分组的SQL语句请教全部的内容，包括:时间段分组的SQL语句请教、sql 时间段分组统计查询、SQL 按日期时间分组语句的写法等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！