select 用户ID
,sum(case when 时间 between '8:30' and '9:30' then 订单数 else 0 end) as '8:30-9:30'
,sum(case when 时间 between '9:31' and '10:30' then 订单数 else 0 end) as '9:31-10:30'
,sum(case when 时间 between '10:31' and '11:30' then 订单数 else 0 end) as '10:31-11:30'
.................
,sum(case when 时间 between '17:31' and '18:30' then 订单数 else 0 end) as '17:31-18:30'
,sum(订单数) as '合计定单数'
from 表
group by 用户ID
,sum(case when 时间 between '8:30' and '9:30' then 订单数 else 0 end) as '8:30-9:30'
,sum(case when 时间 between '9:31' and '10:30' then 订单数 else 0 end) as '9:31-10:30'
,sum(case when 时间 between '10:31' and '11:30' then 订单数 else 0 end) as '10:31-11:30'
.................
,sum(case when 时间 between '17:31' and '18:30' then 订单数 else 0 end) as '17:31-18:30'
,sum(订单数) as '合计定单数'
from 表
group by 用户ID
另外一个表结构
用户ID -- 姓名 -- 年龄
1 -- 张xx -- 27
2 -- 李xx -- 28用户ID -- 时间
1 -- 8:30
1 -- 8:45
1 -- 9:30
1 -- 10:00
1 -- 10:30
1 -- 11:00
1 -- 11:30
1 -- 12:00
1 -- 12:30
1 -- 13:00
2 -- 8:30
2 -- 8:45
2 -- 9:30
2 -- 10:00
显示的结果:
用户ID 姓名 8:30--9:30 9:31--10:30 10:31--11:30 ..17:31:--18:30 合计定单数
1 张xx 3 2 2.. 7
2 李xx 3 1 4
select 用户ID=a.用户ID
,用户名称=(select 姓名 from 另外一个表结构 where 用户ID=a.用户ID) --田间用户名称
,sum(case when 时间 between '8:30' and '9:30' then 订单数 else 0 end) as '8:30-9:30'
,sum(case when 时间 between '9:31' and '10:30' then 订单数 else 0 end) as '9:31-10:30'
,sum(case when 时间 between '10:31' and '11:30' then 订单数 else 0 end) as '10:31-11:30'
.................
,sum(case when 时间 between '17:31' and '18:30' then 订单数 else 0 end) as '17:31-18:30'
--,sum(订单数) as '合计定单数' --去掉合计订单
from 表 a
group by 用户ID
max(case 时间段 when 0 then 总订单数 else 0 end) as '8:30---9:30',
max(case 时间段 when 1 then 总订单数 else 0 end) as '9:31---10:30',
max(case 时间段 when 2 then 总订单数 else 0 end) as '10:31---11:30',
max(case 时间段 when 3 then 总订单数 else 0 end) as '11:31---12:30',
max(case 时间段 when 4 then 总订单数 else 0 end) as '12:31---13:30',
max(case 时间段 when 5 then 总订单数 else 0 end) as '13:31---14:30',
max(case 时间段 when 6 then 总订单数 else 0 end) as '14:31---15:30',
max(case 时间段 when 7 then 总订单数 else 0 end) as '15:31---16:30',
max(case 时间段 when 3 then 总订单数 else 0 end) as '16:31---17:30',
max(case 时间段 when 3 then 总订单数 else 0 end) as '17:31---18:30',
sum(总订单数) as 合计定单数
from
(
select 用户ID,
sum(订单数) as 总订单数,
datediff(hh,convert(char(10),时间,120)+' 08:30',时间) as 时间段
from Test
group by 用户ID,datediff(hh,convert(char(10),时间,120)+' 08:30',时间)
) T
group by 用户ID
max(case 时间段 when 0 then 总订单数 else 0 end) as '8:30---9:30',
max(case 时间段 when 1 then 总订单数 else 0 end) as '9:31---10:30',
max(case 时间段 when 2 then 总订单数 else 0 end) as '10:31---11:30',
max(case 时间段 when 3 then 总订单数 else 0 end) as '11:31---12:30',
max(case 时间段 when 4 then 总订单数 else 0 end) as '12:31---13:30',
max(case 时间段 when 5 then 总订单数 else 0 end) as '13:31---14:30',
max(case 时间段 when 6 then 总订单数 else 0 end) as '14:31---15:30',
max(case 时间段 when 7 then 总订单数 else 0 end) as '15:31---16:30',
max(case 时间段 when 8 then 总订单数 else 0 end) as '16:31---17:30',
max(case 时间段 when 9 then 总订单数 else 0 end) as '17:31---18:30',
sum(总订单数) as 合计定单数
from
(
select 用户ID,
sum(订单数) as 总订单数,
datediff(hh,convert(char(10),时间,120)+' 08:30',时间) as 时间段
from Test
group by 用户ID,datediff(hh,convert(char(10),时间,120)+' 08:30',时间)
) T
group by 用户ID