declare @index int set @index=(select min(day(time)) from tb where datepart(weekday,time)=1) select max(su)su,max(mo)mo,max(tu)tu,max(we)we,max(th)th,max(fr)fr,max(sa)sa from ( select *,rowindex=row_number()over(order by getdate()) from( select case when d=1 then dd end su, case when d=2 then dd end mo, case when d=3 then dd end tu, case when d=4 then dd end we, case when d=5 then dd end th, case when d=6 then dd end fr, case when d=7 then dd end sa from( select day(time) dd,datepart(weekday,time) d from tb )t )tt )ttt group by (rowindex+7-@index)/7
declare @index int set @index=(select top 1 day(time) from tb where datepart(weekday,time)=1) --还是改为top1吧 select max(su)su,max(mo)mo,max(tu)tu,max(we)we,max(th)th,max(fr)fr,max(sa)sa from ( select *,rowindex=row_number()over(order by getdate()) from( select case when d=1 then dd end su, case when d=2 then dd end mo, case when d=3 then dd end tu, case when d=4 then dd end we, case when d=5 then dd end th, case when d=6 then dd end fr, case when d=7 then dd end sa from( select day(time) dd,datepart(weekday,time) d from tb )t )tt )ttt group by (rowindex+7-@index)/7
set @index=(select min(day(time)) from tb where datepart(weekday,time)=1)
select max(su)su,max(mo)mo,max(tu)tu,max(we)we,max(th)th,max(fr)fr,max(sa)sa
from
(
select *,rowindex=row_number()over(order by getdate())
from(
select case when d=1 then dd end su,
case when d=2 then dd end mo,
case when d=3 then dd end tu,
case when d=4 then dd end we,
case when d=5 then dd end th,
case when d=6 then dd end fr,
case when d=7 then dd end sa
from(
select day(time) dd,datepart(weekday,time) d from tb
)t
)tt
)ttt group by (rowindex+7-@index)/7
set @index=(select top 1 day(time) from tb where datepart(weekday,time)=1)
--还是改为top1吧
select max(su)su,max(mo)mo,max(tu)tu,max(we)we,max(th)th,max(fr)fr,max(sa)sa
from
(
select *,rowindex=row_number()over(order by getdate())
from(
select case when d=1 then dd end su,
case when d=2 then dd end mo,
case when d=3 then dd end tu,
case when d=4 then dd end we,
case when d=5 then dd end th,
case when d=6 then dd end fr,
case when d=7 then dd end sa
from(
select day(time) dd,datepart(weekday,time) d from tb
)t
)tt
)ttt group by (rowindex+7-@index)/7
如果这月1号是周三,而我把01数据放到,第一行,第四格的TABEL,02放在第一行第五格的TABEL,这样太麻烦,因为会有七种可能性,我要七次这样的赋值过程。