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

sql語句例子一