sql语句例子一

declare @v_TradingTime datetime
set @v_TradingTime='2008-05-05'

select secucode,   case sum(sign(updownratio))
      when 3 then
       1
      when -3 then
       -1
      else
       0
       end as updown  
                  
         --在这段时间(@v_TradingTime/dateadd(d,-20,@v_TradingTime))内存在的记录条数
     from (select row_number() over(partition by secucode order by tradingtime desc) rn,
         a.*
       from cha_quoteday a
      where markettype in ('A股', 'B股')
        and [close] > 0
        and tradingtime <= @v_TradingTime
        and tradingtime > dateadd(d,-20,@v_TradingTime)
        and exists (select 1 from cha_quoteday c          --@v_TradingTime向前推20天内存在的日期的数据
          where a.secucode = c.secucode
           and c.tradingtime =@v_TradingTime
           and [close] > 0)
               ) b
               where rn<=3
               group by secucode
               having abs(sum(sign(updownratio))) = 3

 

 

2  ROW_NUMBER() OVER 与WITH AS 的用法

WITH orderList AS ( SELECT ROW_NUMBER() OVER (ORDER BY  Articletime DESC)AS Row, *
from Statistics_article
where Articletime between '2008-07-01' and '2008-12-2 0:00:00'  )
SELECT * FROM orderlist WHERE Row between 1 and 20