这样的SQL如何取第二最近的日期的数据？
客户代码 产品代码 日期 价格
c001 p0001 2013-10-18 10
c001 p0001 2013-09-20 8
c001 p0001 2013-08-17 9
c001 p0001 2013-07-24 7
c002 p0002 2013-10-17 3
c002 p0002 2013-10-12 4
c002 p0002 2013-09-12 2
c002 p0002 2013-08-27 5
c002 p0002 2013-07-16 3
--------------------------------------------
c001 p0001 2013-09-20 8
c002 p0002 2013-10-12 4
如何取得这样的结果？
sql
[解决办法]
;with cte(客户代码,产品代码,日期,价格) as
(
select 'c001','p0001','2013-10-18',10
union all select 'c001','p0001','2013-09-20',8
union all select 'c001','p0001','2013-08-17',9
union all select 'c001','p0001','2013-07-24',7
union all select 'c002','p0002','2013-10-17',3
union all select 'c002','p0002','2013-10-12',4
union all select 'c002','p0002','2013-09-12',2
union all select 'c002','p0002','2013-08-27',5
union all select 'c002','p0002','2013-07-16',3
)
select 客户代码,产品代码,日期,价格
from
(select *,rn=ROW_NUMBER() over(partition by 客户代码 order by 日期 desc) from cte)t
where rn=2
/*
客户代码产品代码日期价格
c001p00012013-09-208
c002p00022013-10-124
*/
create table #tb(客户代码 varchar(10),产品代码 varchar(10),日期 varchar(10),价格 varchar(10))
insert into #tb
select 'c001','p0001','2013-10-18',10
union all select 'c001','p0001','2013-09-20',8
union all select 'c001','p0001','2013-08-17',9
union all select 'c001','p0001','2013-07-24',7
union all select 'c002','p0002','2013-10-17',3
union all select 'c002','p0002','2013-10-12',4
union all select 'c002','p0002','2013-09-12',2
union all select 'c002','p0002','2013-08-27',5
union all select 'c002','p0002','2013-07-16',3
;with cte as(
select *,id=ROW_NUMBER() over(partition by 客户代码 order by 日期 desc) from #tb)
select 客户代码,产品代码,日期,价格 from cte where id=2
客户代码 产品代码 日期 价格
---------- ---------- ---------- ----------
c001 p0001 2013-09-20 8
c002 p0002 2013-10-12 4
(2 行受影响)
;with cte(客户代码,产品代码,日期,价格) as
(
select 'c001','p0001','2013-10-18',10
union all select 'c001','p0001','2013-09-20',8
union all select 'c001','p0001','2013-08-17',9
union all select 'c001','p0001','2013-07-24',7
union all select 'c002','p0002','2013-10-17',3
union all select 'c002','p0002','2013-10-12',4
union all select 'c002','p0002','2013-09-12',2
union all select 'c002','p0002','2013-08-27',5
union all select 'c002','p0002','2013-07-16',3
)
select 客户代码,产品代码,日期,价格
from
(
select *,
--如果有两天数据是同一天的，那么这两条数据都是第二最近的，那么都会出来
dense_rank() over(partition by 客户代码 order by 日期 desc) as rownum
from cte
)t
where rownum=2
/*
客户代码产品代码日期 价格
c001p00012013-09-208
c002p00022013-10-124
*/
;with cte(客户代码,产品代码,日期,价格) as
(
select 'c001','p0001','2013-10-18',10
union all select 'c001','p0001','2013-09-20',8
union all select 'c001','p0001','2013-08-17',9
union all select 'c001','p0001','2013-07-24',7
union all select 'c002','p0002','2013-10-17',3
union all select 'c002','p0002','2013-10-12',4
union all select 'c002','p0002','2013-09-12',2
union all select 'c002','p0002','2013-08-27',5
union all select 'c002','p0002','2013-07-16',3
)
select 客户代码,产品代码,日期,价格 from
(
select *,(ROW_NUMBER() over( partition by 客户代码 order by 日期 desc )) as rn from cte
) b where b.rn=2
