sql语句例子一
declare @v_TradingTime datetimeset @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 > 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 > 0)
) b
where rn<=3
group by secucode
having abs(sum(sign(updownratio))) = 3http://cnc.imgcache.qq.com/ac/b.gif
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
页:
[1]