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
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
