当月第几个星期:
方法一: datepart(week,OrderDate)-datepart(week,OrderDate-day(OrderDate))+1
方法二: datediff(week,DATEADD(mm, DATEDIFF(mm,0, OrderDate), 0), OrderDate)+1
--行列转换,显示的列,必需出现在聚合函数,或 group by
select uvw.upclassname ,
sum(case when (datepart(wk,uvw.orderdate)=datepart(wk,'2009-6-1')) then (uvw.orderqty) else 0 end ) 'first week' ,
sum(case when (datepart(wk,orderdate)-1=datepart(wk,'2009-6-1')) then orderqty else 0 end ) 'second week',
sum(case when (datepart(wk,orderdate)-2=datepart(wk,'2009-6-1')) then orderqty else 0 end ) 'third week',
sum(case when (datepart(wk,orderdate)-3=datepart(wk,'2009-6-1')) then orderqty else 0 end ) 'fourth week',
sum(case when (datepart(wk,orderdate)-4=datepart(wk,'2009-6-1')) then orderqty else 0 end ) 'fifth week'
from ( select upclassname,sum(orderqty) orderqty,orderdate from uvw_orderlistforqty
group by upclassname,orderdate having sum(orderqty) >0 ) as uvw
查看#建立的临时表
select * from sysobjects where name like '#t%'
Drop Table #t