常用SQL语句

当你遇到好的上司，你就会冲忙挑战:

当前月第几个星期：
方法一： 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

 

 

 

----SQL随机数
---- 用来组合时间
----dmyDateTime+' 18:'+convert(char(2),ABS(CHECKSUM(NEWID()))%20+40)
select ABS(CHECKSUM(NEWID())) %10