4周信息 按照 周 星期 统计查询

if object_id('tempdb..#temp') is not null--临时表存在则删除
begin
 drop table #temp
end

create table #temp--建立临时表
(
 Id int identity primary key,
 ColumnName varchar(100),
 Week_1 varchar(100) default 0,
 Week_2 varchar(100) default 0,
 Week_3 varchar(100) default 0,
 Week_4 varchar(100) default 0
)

--定义四周的日期
declare @D1 varchar(10)
declare @D11 varchar(10)
declare @D2 varchar(10)
declare @D22 varchar(10)
declare @D3 varchar(10)
declare @D33 varchar(10)
declare @D4 varchar(10)
declare @D44 varchar(10)
set @D1= CONVERT(varchar(10), DATEADD(wk, DATEDIFF(wk,0,getdate()), -21), 23)
set @D11= CONVERT(varchar(10), DATEADD(wk, DATEDIFF(wk,0,getdate()), -15), 23)
set @D2= CONVERT(varchar(10), DATEADD(wk, DATEDIFF(wk,0,getdate()), -14), 23)
set @D22= CONVERT(varchar(10), DATEADD(wk, DATEDIFF(wk,0,getdate()), -8), 23)
set @D3= CONVERT(varchar(10), DATEADD(wk, DATEDIFF(wk,0,getdate()), -7), 23)
set @D33= CONVERT(varchar(10), DATEADD(wk, DATEDIFF(wk,0,getdate()), -1), 23)
set @D4= CONVERT(varchar(10), DATEADD(wk, DATEDIFF(wk,0,getdate()), 0), 23)
set @D44= CONVERT(varchar(10), DATEADD(wk, DATEDIFF(wk,0,getdate()), 6), 23)

--生成表头和左列信息
insert into #temp (ColumnName,Week_1,Week_2,Week_3,Week_4) values ('',substring(@D1,6,11)+'至'+substring(@D11,6,11),substring(@D2,6,11)+'至'+substring(@D22,6,11),substring(@D3,6,11)+'至'+substring(@D33,6,11),substring(@D4,6,11)+'至'+substring(@D44,6,11))
insert into #temp (ColumnName) values ('星期一')
insert into #temp (ColumnName) values ('星期二')
insert into #temp (ColumnName) values ('星期三')
insert into #temp (ColumnName) values ('星期四')
insert into #temp (ColumnName) values ('星期五')
insert into #temp (ColumnName) values ('星期六')
insert into #temp (ColumnName) values ('星期日')
insert into #temp (ColumnName) values ('周排行成绩')
insert into #temp (ColumnName) values ('名次')
insert into #temp (ColumnName) values ('获得奖励')
insert into #temp (ColumnName) values ('领取奖励')

--统计信息
--周一至周日的成绩
DECLARE @i int
Set @i = 0
WHILE @i < 7
BEGIN

update #temp set Week_1=(select (CASE WHEN (sum(bean) is null) THEN 0 ELSE sum(bean) END) from match where datediff(day,matchtime,(convert(datetime,@D1)+@i))=0),
    Week_2=(select (CASE WHEN (sum(bean) is null) THEN 0 ELSE sum(bean) END) from match where datediff(day,matchtime,(convert(datetime,@D2)+@i))=0),
    Week_3=(select (CASE WHEN (sum(bean) is null) THEN 0 ELSE sum(bean) END) from match where datediff(day,matchtime,(convert(datetime,@D3)+@i))=0),
    Week_4=(select (CASE WHEN (sum(bean) is null) THEN 0 ELSE sum(bean) END) from match where datediff(day,matchtime,(convert(datetime,@D4)+@i))=0)
    where id=(@i+2)
print (@i+2)
Set @i =@i +1
END
--周排行成绩

update #temp set Week_1=(select (CASE WHEN (sum(bean) is null) THEN 0 ELSE sum(bean) END) from match where datediff(week,matchtime,@D1)=0),
    Week_2=(select (CASE WHEN (sum(bean) is null) THEN 0 ELSE sum(bean) END) from match where datediff(week,matchtime,@D2)=0),
    Week_3=(select (CASE WHEN (sum(bean) is null) THEN 0 ELSE sum(bean) END) from match where datediff(week,matchtime,@D3)=0),
    Week_4=(select (CASE WHEN (sum(bean) is null) THEN 0 ELSE sum(bean) END) from match where datediff(week,matchtime,@D4)=0)
    where id=9

select * from #temp