近期价格处于降价趋势(至少调了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
*/
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
*/
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步