select
sum(出货量) as 总出货量,
sum(case convert(char(5),时间,108) between '14:00' and '15:59' then 出货量 else 0 end),
sum(case convert(char(5),时间,108) between '16:00' and '19:59' then 出货量 else 0 end),
sum(case convert(char(5),时间,108) between '20:00' and '23:59' then 出货量 else 0 end)
from
表
where
datediff(dd,时间,'2007-11-20')=0
sum(出货量) as 总出货量,
sum(case convert(char(5),时间,108) between '14:00' and '15:59' then 出货量 else 0 end),
sum(case convert(char(5),时间,108) between '16:00' and '19:59' then 出货量 else 0 end),
sum(case convert(char(5),时间,108) between '20:00' and '23:59' then 出货量 else 0 end)
from
表
where
datediff(dd,时间,'2007-11-20')=0
sum(出货量) as 总出货量,
sum(case when convert(char(5),时间,108) between '14:00' and '15:59' then 出货量 else 0 end),
sum(case when convert(char(5),时间,108) between '16:00' and '19:59' then 出货量 else 0 end),
sum(case when convert(char(5),时间,108) between '20:00' and '23:59' then 出货量 else 0 end)
from
表
where
datediff(dd,时间,'2007-11-20')=0
create table linsy008
(
id int identity(1,1) primary key not null,
name varchar(10),
num int,
dTime datetime
)select getdate()
delete linsy008
insert into linsy008
select 'AAA',10,'2007-11-20 14:28:03' union all
select 'AAA',10,'2007-11-20 14:30:03' union all
select 'AAA',10,'2007-11-20 15:28:03' union all
select 'AAA',10,'2007-11-20 16:28:03' union all
select 'AAA',10,'2007-11-20 16:30:03' union all
select 'AAA',10,'2007-11-20 20:28:03' union all
select 'AAA',10,'2007-11-20 23:28:03' union all
select 'AAA',10,'2007-11-20 21:28:03' union all
select 'AAA',10,'2007-11-20 22:28:03' union all
select 'AAA',100,'2007-11-21 22:28:03'
select
sum(num) as 总出货量,
sum(case when convert(char(5),dTime,108) between '14:00' and '15:59' then num else 0 end)as'14:00-16:00' ,
sum(case when convert(char(5),dTime,108) between '16:00' and '19:59' then num else 0 end)as'16:00-20:00',
sum(case when convert(char(5),dTime,108)between '20:00' and '23:59' then num else 0 end)as '20:00-24:00'
from
linsy008
where
datediff(dd,dTime,'2007-11-20')=0
/*
总出货量 14:00-16:00 16:00-20:00 20:00-24:00
90 30 20 40
*/
set @dt = '2007-11-20'select 出货时间 , sum(出货量) 出货量 from
(
select 出货时间 = case
when convert(varchar(10),时间字段,120) = @dt and datepart(hh,时间字段) between 14 and 15 then '14:00-16:00'
when convert(varchar(10),时间字段,120) = @dt and datepart(hh,时间字段) between 16 and 19 then '16:00-20:00'
when convert(varchar(10),时间字段,120) = @dt and datepart(hh,时间字段) between 20 and 23 then '20:00-24:00'
end,出货量
from tb
) t
group by 出货时间
union all
select 出货时间 = '一天24小时总的出货量' , sum(出货量) 出货量 from tb where convert(varchar(10),时间字段,120) = @dt
set @dt = '2007-11-20'select 出货时间 , sum(出货量) 出货量 from
(
select 出货时间 = case
when convert(varchar(10),时间字段,120) = @dt and datepart(hh,时间字段) between 14 and 15 then '14:00-16:00'
when convert(varchar(10),时间字段,120) = @dt and datepart(hh,时间字段) between 16 and 19 then '16:00-20:00'
when convert(varchar(10),时间字段,120) = @dt and datepart(hh,时间字段) between 20 and 23 then '20:00-24:00'
end,出货量
from tb
) t
group by 出货时间
union all
select 出货时间 = '一天24小时总的出货量' , sum(出货量) 出货量 from tb where convert(varchar(10),时间字段,120) = @dt
------------------------------------------------------------------
如果是多张表,且结构一样(如果不一样,自己提取需要的数据。)select 出货时间 , sum(出货量) 出货量 from
(
select 出货时间 = case
when convert(varchar(10),时间字段,120) = @dt and datepart(hh,时间字段) between 14 and 15 then '14:00-16:00'
when convert(varchar(10),时间字段,120) = @dt and datepart(hh,时间字段) between 16 and 19 then '16:00-20:00'
when convert(varchar(10),时间字段,120) = @dt and datepart(hh,时间字段) between 20 and 23 then '20:00-24:00'
end,出货量
from
(
select * from tb1
union all
select * from tb2
...
union all
select * from tbn
) m
) t
group by 出货时间
union all
select 出货时间 = '一天24小时总的出货量' , sum(出货量) 出货量 from
(
select * from tb1
union all
select * from tb2
...
union all
select * from tbn
) m
where convert(varchar(10),时间字段,120) = @dt[/code]