--测试数据
declare @表 table(zje int,rq datetime)
insert into @表
select 1,'2004-1-03'
union all select 2,'2004-1-13'
union all select 3,'2004-1-13'
union all select 4,'2004-1-10'
union all select 5,'2004-1-02'
union all select 6,'2004-1-02'
union all select 7,'2004-1-03'
union all select 8,'2004-1-05'
union all select 9,'2004-1-06'
union all select 10,'2004-1-07'
union all select 11,'2004-1-08'
union all select 1,'2004-1-08'
union all select 13,'2004-1-09'
union all select 14,'2004-1-23'
union all select 15,'2004-1-01'
union all select 16,'2004-1-03'
--查询处理
select 区间=right(convert(char(10),min(rq),102),5)
+'~'+right(convert(char(10),max(rq),102),5)
,周日=sum(case wk when 1 then zje else 0 end)
,周一=sum(case wk when 2 then zje else 0 end)
,周二=sum(case wk when 3 then zje else 0 end)
,周三=sum(case wk when 4 then zje else 0 end)
,周四=sum(case wk when 5 then zje else 0 end)
,周五=sum(case wk when 6 then zje else 0 end)
,周六=sum(case wk when 7 then zje else 0 end)
from(
select zje,w=datename(week,rq),wk=datepart(weekday,rq),rq
from @表
where rq between '2004-01-01' and '2004-01-10'
) a
group by w
order by 区间
select 区间=right(convert(char(10),min(rq),102),5)
+'~'+right(convert(char(10),max(rq),102),5)
,周日=sum(case wk when 1 then zje else 0 end)
,周一=sum(case wk when 2 then zje else 0 end)
,周二=sum(case wk when 3 then zje else 0 end)
,周三=sum(case wk when 4 then zje else 0 end)
,周四=sum(case wk when 5 then zje else 0 end)
,周五=sum(case wk when 6 then zje else 0 end)
,周六=sum(case wk when 7 then zje else 0 end)
from(
select zje,w=datename(week,rq),wk=datepart(weekday,rq),rq
from @表
where rq between '2004-01-01' and '2004-01-10'
) a
group by w
order by 区间
select 区间=convert(char(10),min(rq),102)+'~'+convert(char(10),max(rq),102)
,周日=sum(case wk when 1 then zje else 0 end)
,周一=sum(case wk when 2 then zje else 0 end)
,周二=sum(case wk when 3 then zje else 0 end)
,周三=sum(case wk when 4 then zje else 0 end)
,周四=sum(case wk when 5 then zje else 0 end)
,周五=sum(case wk when 6 then zje else 0 end)
,周六=sum(case wk when 7 then zje else 0 end)
from(
select zje,w=datename(week,rq),wk=datepart(weekday,rq),rq
from 表
where rq between '2004-01-01' and '2004-01-10'
) a
group by w
order by 区间
SET DATEFIRST 7 --加上这句
select 周日=sum(case wk when 1 then zje else 0 end)
,周一=sum(case wk when 2 then zje else 0 end)
,周二=sum(case wk when 3 then zje else 0 end)
,周三=sum(case wk when 4 then zje else 0 end)
,周四=sum(case wk when 5 then zje else 0 end)
,周五=sum(case wk when 6 then zje else 0 end)
,周六=sum(case wk when 7 then zje else 0 end)
from(
select zje,wk=datepart(weekday,rq)
from 表
where rq between '2004-01-01' and '2004-01-10'
) a
select 周日=sum(case wk when 1 then zje else 0 end)
,周一=sum(case wk when 2 then zje else 0 end)
,周二=sum(case wk when 3 then zje else 0 end)
,周三=sum(case wk when 4 then zje else 0 end)
,周四=sum(case wk when 5 then zje else 0 end)
,周五=sum(case wk when 6 then zje else 0 end)
,周六=sum(case wk when 7 then zje else 0 end)
from(
select zje,wk=datepart(weekday,rq)
from 表
where rq between '2004-01-01' and '2004-01-10'
) a
select sum(case when datepart(weekday,rq) = 1 then zje else 0 end) as 周日,
sum(case when datepart(weekday,rq) = 2 then zje else 0 end) as 周一,
sum(case when datepart(weekday,rq) = 3 then zje else 0 end) as 周二,
sum(case when datepart(weekday,rq) = 4 then zje else 0 end) as 周三,
sum(case when datepart(weekday,rq) = 5 then zje else 0 end) as 周四,
sum(case when datepart(weekday,rq) = 6 then zje else 0 end) as 周五,
sum(case when datepart(weekday,rq) = 7 then zje else 0 end) as 周六
from 表
where rq between '2004-01-01' and '2004-01-10'