sql语句例子一-优快云博客

本文介绍了使用SQL进行复杂数据筛选的方法，包括利用ROW_NUMBER()进行排序及分组统计，通过WITH AS子句优化查询效率。重点讲解了如何针对特定时间段内的股票交易数据进行高效处理，以及如何从大量文章中快速选取指定范围的内容。

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