经典sql代码--近期价格处于降价趋势（至少调了3次）的所有商品

    经典sql代码--近期价格处于降价趋势（至少调了3次）的所有商品，搞商城程序的朋友可以参考下本文，会有不少收获的。

--近期价格处于降价趋势（至少调了3次）的所有商品
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([日期] datetime,[商品编码] varchar(6),[价格] numeric(3,1))
insert [tb]
select '20091101','033001', 11.9 union all
select '20091101','033002', 30.5 union all
select '20091101','033003', 2 union all

select '20091102','033001', 11.9  union all
select '20091102','033002', 30    union all
select '20091102','033003', 2      union all

select '20091103','033001', 11.5  union all
select '20091103','033002', 30.5  union all
select '20091103','033003', 2.5    union all

select '20091104','033001', 11.5  union all
select '20091104','033002', 30.5  union all
select '20091104','033003', 2.5    union all

select '20091105','033001', 11.5  union all
select '20091105','033002', 30.5  union all
select '20091105','033003', 2.8    union all

select '20091106','033001', 11.5  union all
select '20091106','033002', 30    union all
select '20091106','033003', 2.9    union all

select '20091107','033001', 11    union all
select '20091107','033002', 30    union all
select '20091107','033003', 2.9    union all

select '20091108','033001', 10.8  union all
select '20091108','033002', 30    union all
select '20091108','033003', 2.9 
   
--------------开始查询--------------------------
with cte2 as 
(select row_number() over (partition by 商品编码 order by 价格 desc) as denserank,* from tb)
select * from cte2 
where 商品编码 in (select 商品编码 from cte2 where 日期='20091108' and denserank>=3) 
order by  商品编码,日期

/*
denserank 日期    商品编码  价格
----------------------------------
  1      2009-11-01     033001    11.9
  2      2009-11-02     033001    11.9
  3      2009-11-03     033001    11.5
  4      2009-11-04     033001    11.5
  5      2009-11-05     033001    11.5
  6      2009-11-06     033001    11.5
  7      2009-11-07     033001    11.0
  8      2009-11-08     033001    10.8
  4      2009-11-01     033002    30.5
  5      2009-11-02     033002    30.0
  3      2009-11-03     033002    30.5
  2      2009-11-04     033002    30.5
  1      2009-11-05     033002    30.5
  6      2009-11-06     033002    30.0
  8      2009-11-07     033002    30.0
  7      009-11-08     033002    30.0
*/

原文链接：http://www.cnblogs.com/zengxiangzhan/archive/2010/01/03/1638156.html