declare @t table(日期 datetime,数量 int)
Insert into @t
select '2006-05-01 17:11:34.000', 8.0
union select '2006-05-11 11:33:35.000', 8.0
union select '2006-05-12 11:38:06.000', 8.0
union select '2006-05-12 11:55:08.000', 8.0
union select '2006-05-12 19:55:08.000', 8.0 Select [日期]=Convert(Char(10),日期,120),
[白 ]=sum(case when datepart(hh,日期) between 8 and 18 then 数量 else 0 end),
[夜 ]=sum(case when datepart(hh,日期)=0 or datepart(hh,日期)>18 then 数量 else 0 end)
from @t
group by Convert(Char(10),日期,120)
Insert into @t
select '2006-05-01 17:11:34.000', 8.0
union select '2006-05-11 11:33:35.000', 8.0
union select '2006-05-12 11:38:06.000', 8.0
union select '2006-05-12 11:55:08.000', 8.0
union select '2006-05-12 19:55:08.000', 8.0 Select [日期]=Convert(Char(10),日期,120),
[白 ]=sum(case when datepart(hh,日期) between 8 and 18 then 数量 else 0 end),
[夜 ]=sum(case when datepart(hh,日期)=0 or datepart(hh,日期)>18 then 数量 else 0 end)
from @t
group by Convert(Char(10),日期,120)
insert into @t select '2006-05-01 17:11:34.000' ,8.0
union all select '2006-05-11 11:33:35.000' ,8.0
union all select '2006-05-11 11:38:06.000' ,8.0
union all select '2006-05-11 11:55:08.000' ,8.0
union all select '2006-05-11 11:55:24.000' ,10.0
union all select '2006-05-11 12:03:02.000' ,8.0
union all select '2006-05-11 12:03:31.000' ,8.0
union all select '2006-05-11 12:07:21.000' ,8.0
union all select '2006-05-11 12:14:06.000' ,8.0
union all select '2006-05-11 12:18:04.000' ,6.0select 日期=convert(char(10),日期,120),
白=sum(case when convert(char(5),日期,108) between '08:00' and '18:00' then 数量 else 0 end),
夜=sum(case when convert(char(5),日期,108) between '19:00' and '24:00' then 数量 else 0 end)
from @t
group by convert(char(10),日期,120)
白=sum(case when datepart(hh,日期)>8 and datepart(hh,日期)<=18 then 数量 end),
夜=sum(case when datepart(hh,日期)>18 and datepart(hh,日期)<=24 then 数量 end)
from 表
group by convert(char(10),日期,120)
Select
Convert(Varchar(10),日期,120) As 日期,
SUM(Case When Convert(Varchar,日期,108) Between '08:00:00' And '17:59:59' Then 数量 Else 0 End )As 白,
SUM(Case When Convert(Varchar,日期,108) Between '18:00:00' And '23:59:59' Then 数量 Else 0 End )As 夜
From 表
Group By Convert(Varchar(10),日期,120)
Order By 日期
insert into @t select '2006-05-01 17:11:34.000' ,8.0
union all select '2006-05-11 11:33:35.000' ,8.0
union all select '2006-05-11 11:38:06.000' ,8.0
union all select '2006-05-11 11:55:08.000' ,8.0
union all select '2006-05-11 11:55:24.000' ,10.0
union all select '2006-05-11 12:03:02.000' ,8.0
union all select '2006-05-11 12:03:31.000' ,8.0
union all select '2006-05-11 12:07:21.000' ,8.0
union all select '2006-05-11 12:14:06.000' ,8.0
union all select '2006-05-11 12:18:04.000' ,6.0Select
Convert(Varchar(10),日期,120) As 日期,
SUM(Case When Convert(Varchar,日期,108) Between '08:00:00' And '17:59:59' Then 数量 Else 0 End )As 白,
SUM(Case When Convert(Varchar,日期,108) Between '18:00:00' And '23:59:59' Then 数量 Else 0 End )As 夜
From @t
Group By Convert(Varchar(10),日期,120)
Order By 日期/*
日期 白 夜
2006-05-01 8.0 .0
2006-05-11 72.0 .0
*/
白=sum(case when datepart(hh,日期)>=8 and datepart(hh,日期)<=18 then 数量 else 0 end),
夜=sum(case when datepart(hh,日期)>18 and datepart(hh,日期)<=24 then 数量 else 0 end)
from 表
group by convert(char(10),日期,120)
Select
Convert(Varchar(10),日期,120) As 日期,
SUM(Case When Convert(Varchar,日期,108) Between '08:00:00' And '17:59:59' Then 数量 Else 0 End )As 白,
SUM(Case When Convert(Varchar,日期,108) Between '18:00:00' And '23:59:59' Then 数量 Else 0 End )As 夜
From @t
Group By Convert(Varchar(10),日期,120)
Order By 日期
第一次来SQL发贴,老大们都在,比.NET人气旺多了,^_^试了一下,问题解决了一半,刚才讲错了,应该是:
白班(8:00~18:00)
夜班(18:00~次日8:00)
http://community.csdn.net/Expert/topic/4793/4793314.xml?temp=7.352847E-02