找出商品在最近2次不同价格第一次出现的日期

Question

之前发了一贴，我的描述有点问题，导致大家给的结果不符合我的需求，本次重发
我的需求是：
找到商品最后的2次不同价格第一次出现的日期。注意，不是最后的两个价格。下例中：散热片最后的2次价格是1.2和1.6，分别取他们第一次出现的时间，分别是2008.05.08和2008.05.31源数据如下：
单号 物料号 物料描述 价格 日期
205 B07040 散热片 1.6 2007.12.04
755 B07040 散热片 1.3 2008.01.10
2475 B07040 散热片 1.6 2008.05.08 --倒数第二次价格首次出现，结果取这一行
2526 B07040 散热片 1.6 2008.05.09 --倒数第二次价格，但不是首次出现该价格
2831 B07040 散热片 1.2 2008.05.31 --最后一次价格首次出现，结果取这一行
5439 B07040 散热片 1.2 2009.02.27 --最后一次价格，但不是首次出现该价格，结果不取这一行
568 C05050 异形铝 21 2007.12.28
707 C05050 异形铝 21 2008.01.03
2496 C05050 异形铝 19 2008.04.07
3643 C05050 异形铝 20 2009.03.04 --倒数第二次价格首次出现，结果取这一行
7167 C05050 异形铝 20 2009.07.23 --倒数第二次价格，但该价格非首次出现，结果不取这一行
7444 C05050 异形铝 19 2009.08.11 ---最后一次价格，首次出现，结果取这一行希望得到的结果如下：
料号 描述 最后一次价格 最后一次时间 倒数第二次价格 倒数第二次时间
B07040 散热片 1.2 2008.05.31 1.6 2008.05.08
C05050 异形铝 19 2009.08.11 20 2009.03.04

Answer 1

USE tempdb
GO
IF OBJECT_ID('Product') IS NOT NULL
   DROP TABLE Product
GO
CREATE TABLE Product(单号 int primary key,物料号 varchar(10),物料描述 varchar(20),
价格 decimal(9,2),日期 datetime)
go
insert into Product
select 205,'B07040','散热片',1.6,'2007.12.04' union all
select 755,'B07040','散热片',1.3,'2008.01.10' union all
select 2475,'B07040','散热片',1.6,'2008.05.08' union all
select 2526,'B07040','散热片',1.6,'2008.05.09' union all
select 2831,'B07040','散热片',1.2,'2008.05.31' union all
select 5439,'B07040','散热片',1.2,'2009.02.27' union all
select 568,'C05050','异形铝',21,'2007.12.28' union all
select 707,'C05050','异形铝',21,'2008.01.03' union all
select 2496,'C05050','异形铝',19,'2008.04.07' union all
select 3643,'C05050','异形铝',20,'2009.03.04' union all
select 7167,'C05050','异形铝',20,'2009.07.23' union all
select 7444,'C05050','异形铝',19,'2009.08.11'
go
with ax as(
select  *,row_number() over(partition by 物料描述 order by 日期) rn from Product),
bx as(
select  a.*,row_number() over(partition by a.物料描述 order by a.日期 desc) rn1 from ax a,ax b
where a.rn=b.rn+1 and (a.价格<>b.价格 or a.rn=1) and a.物料描述=b.物料描述),
cx as(
select * from bx where rn1<3)
select a.物料号,a.物料描述,a.价格 最后一次价格 ,a.日期 最后一次时间 ,
b.价格 倒数第二次价格 ,b.日期 倒数第二次时间
from cx a,cx b
where a.rn1=1 and b.rn1=2 and a.物料描述=b.物料描述
B07040 散热片 1.20        2008-05-31 00:00:00.000        1.60         2008-05-08 00:00:00.000
C05050 异形铝 19.00 2009-08-11 00:00:00.000 20.00 2009-03-04 00:00:00.000

Answer 2

USE tempdb
GO
IF OBJECT_ID('Product') IS NOT NULL
   DROP TABLE Product
GO
CREATE TABLE Product(单号 int primary key,物料号 varchar(10),物料描述 varchar(20),
价格 decimal(9,2),日期 datetime)
go
insert into Product
select 205,'B07040','散热片',1.6,'2007.12.04' union all
select 755,'B07040','散热片',1.3,'2008.01.10' union all
select 2475,'B07040','散热片',1.6,'2008.05.08' union all
select 2526,'B07040','散热片',1.6,'2008.05.09' union all
select 2831,'B07040','散热片',1.2,'2008.05.31' union all
select 5439,'B07040','散热片',1.2,'2009.02.27' union all
select 568,'C05050','异形铝',21,'2007.12.28' union all
select 707,'C05050','异形铝',21,'2008.01.03' union all
select 2496,'C05050','异形铝',19,'2008.04.07' union all
select 3643,'C05050','异形铝',20,'2009.03.04' union all
select 7167,'C05050','异形铝',20,'2009.07.23' union all
select 7444,'C05050','异形铝',19,'2009.08.11'
go
with ax as(
select  *,row_number() over(partition by 物料描述 order by 日期) rn from Product),
bx as(
select  a.*,row_number() over(partition by a.物料描述 order by a.日期 desc) rn1 from ax a,ax b
where a.rn=b.rn+1 and (a.价格<>b.价格 or a.rn=1) and a.物料描述=b.物料描述),
cx as(
select * from bx where rn1<3)
select a.物料号,a.物料描述,a.价格 最后一次价格 ,a.日期 最后一次时间 ,
b.价格 倒数第二次价格 ,b.日期 倒数第二次时间
from cx a,cx b
where a.rn1=1 and b.rn1=2 and a.物料描述=b.物料描述

Answer 3

--建立测试环境
IF OBJECT_ID('tb') IS NOT NULL  DROP TABLE tb
GO
CREATE TABLE tb(单号 int, 物料号 varchar(10),物料描述 varchar(10),价格 numeric(10,2) ,日期 datetime)
GO
INSERT TB
select '205','B07040','散热片','1.1','2007.12.04'union all
select '755','B07040','散热片','1.3','2008.01.10'union all
select '2475','B07040','散热片','1.6','2008.05.08'union all
select '2526','B07040','散热片','1.6','2008.05.09'union all
select '2831','B07040','散热片','1.2','2008.05.31'union all
select '5439','B07040','散热片','1.2','2009.02.27'union all
select '568','C05050','异形铝','21','2007.12.28'union all
select '707','C05050','异形铝','21','2008.01.03'union all
select '2496','C05050','异形铝','19','2008.04.07'union all
select '3643','C05050','异形铝','21','2009.03.04'union all
select '7167','C05050','异形铝','20','2009.07.23'union all
select '7444','C05050','异形铝','19','2009.08.11'
go
--查询
;with t1 as(
select rn=row_number()over(partition by 物料号 order by 日期 desc ),* from tb
),t2 as(
select rn1=row_number()over(partition by 物料号 order by rn ),* from  t1 t
where exists(select 1 from t1 where 物料号=t.物料号 and rn=t.rn+1 and 价格<>t.价格)
)
select 物料号, 物料描述, 
最后一次价格 =max(case when rn1=1 then 价格 end),
最后一次时间 =max(case when rn1=1 then 日期 end),
倒数第二次价格 =max(case when rn1=2 then 价格 end),
倒数第二次时间 =max(case when rn1=2 then 日期 end)
from t2 
group by 物料号, 物料描述
--结果
/*
物料号        物料描述       最后一次价格                                  最后一次时间                  倒数第二次价格                                 倒数第二次时间
---------- ---------- --------------------------------------- ----------------------- --------------------------------------- -----------------------
B07040     散热片        1.20                                    2008-05-31 00:00:00.000 1.60                                    2008-05-08 00:00:00.000
C05050     异形铝        19.00                                   2009-08-11 00:00:00.000 20.00                                   2009-07-23 00:00:00.000
警告: 聚合或其他 SET 操作消除了空值。
*/

Answer 4

写了一个，结果发现写的复杂的一米
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go 
create table #tb (单号 int,物料号 varchar(6),物料描述 varchar(6),价格 numeric(12,1),日期 datetime)
insert into #tb
select 205,'B07040','散热片',1.6,'2007.12.04' union all
select 755,'B07040','散热片',1.3,'2008.01.10' union all
select 2475,'B07040','散热片',1.6,'2008.05.08' union all
select 2526,'B07040','散热片',1.6,'2008.05.09' union all
select 2831,'B07040','散热片',1.2,'2008.05.31' union all
select 5439,'B07040','散热片',1.2,'2009.02.27' union all
select 568,'C05050','异形铝',21,'2007.12.28' union all
select 707,'C05050','异形铝',21,'2008.01.03' union all
select 2496,'C05050','异形铝',19,'2008.04.07' union all
select 3643,'C05050','异形铝',20,'2009.03.04' union all
select 7167,'C05050','异形铝',20,'2009.07.23' union all
select 7444,'C05050','异形铝',19,'2009.08.11'alter table #tb add id int
declare @n int,@m decimal(10,1),@s varchar(6)
set @n=0
set @m=0
set @s=''update #tb set @n=case when 价格=@m and 物料号=@s then @n+1 else 1 end,@m=价格,@s=物料号,id=@nselect 
* into #t
 from #tb t
where id=1 and (select count(*) from #tb where 物料号=t.物料号 and id=1 and 日期>t.日期)<2select 
物料号 ,
物料描述,
最后一次价格=(select top 1 价格 from #t where 物料号=t.物料号 order by 日期 desc ),
最后一次时间=max(日期),
最后一次价格=(select top 1 价格 from #t where 物料号=t.物料号 order by 日期 asc ),
最后一次时间=min(日期)
from #t t
group by 物料号 , 物料描述物料号    物料描述   最后一次价格                                  最后一次时间                  最后一次价格                                  最后一次时间
------ ------ --------------------------------------- ----------------------- --------------------------------------- -----------------------
B07040 散热片    1.2                                     2008-05-31 00:00:00.000 1.6                                     2008-05-08 00:00:00.000
C05050 异形铝    19.0                                    2009-08-11 00:00:00.000 20.0                                    2009-03-04 00:00:00.000(2 行受影响)
drop table #t

Answer 5

create table #tmp(单号 int,物料号 varchar(50),物料描述 varchar(50),价格 numeric(11,3),日期 varchar(50))
insert into #tmp
select 205,'B07040','散热片',1.6,'2007.12.04' union all
select 755,'B07040','散热片',1.3,'2008.01.10' union all
select 2475,'B07040','散热片',1.6,'2008.05.08' union all
select 2526,'B07040','散热片',1.6,'2008.05.09' union all
select 2831,'B07040','散热片',1.2,'2008.05.31' union all
select 5439,'B07040','散热片',1.2,'2009.02.27' union all
select 568,'C05050','异形铝',21,'2007.12.28' union all
select 707,'C05050','异形铝',21,'2008.01.03' union all
select 2496,'C05050','异形铝',19,'2008.04.07' union all
select 3643,'C05050','异形铝',20,'2009.03.04' union all
select 7167,'C05050','异形铝',20,'2009.07.23';
with tb as 
(
select row_number() over(order by 日期) id, 
单号,物料号 ,物料描述,价格 ,日期 from #tmp 
),
tc as
(
select *from tb where id in (
select top 2 max(id)  from tb a where   tb.物料号=a.物料号 
group by 价格,物料号 order by  max(id) desc)), td as (select min(b.日期)日期,a.物料号,a.价格,b.物料描述,row_number() over(partition by a.物料号 order by a.id desc ) id
From tc a join #tmp b on a.物料号=b.物料号 and a.价格=b.价格
group by a.物料号,a.价格,a.id,b.物料描述)select  物料号,物料描述,
max(case when id=1 then 价格 else 0 end)最后一次价格   ,
max(case when id=1 then  日期 else '' end)最后一次时间 , 
max(case when id=2 then 价格 else 0 end)倒数第二次价格,
max(case when id=2 then 日期 else '' end)倒数第二次时间  
from td group by 物料号,物料描述

Answer 6

测试后发现上面给出的答案有一个问题，如果该商品从头到尾只有一个价格，或从头到尾只有两个价格，则该商品不能显示出来。比如，源数据如下：
IF OBJECT_ID('tb') IS NOT NULL  DROP TABLE tb
GO
CREATE TABLE tb(单号 int, 物料号 varchar(10),物料描述 varchar(10),价格 numeric(10,2) ,日期 datetime)
GO
INSERT TB
select '205','B07040','散热片','1.1','2007.12.04'union all
select '755','B07040','散热片','1.3','2008.01.10'union all
select '2475','B07040','散热片','1.6','2008.05.08'union all
select '2526','B07040','散热片','1.6','2008.05.09'union all
select '2831','B07040','散热片','1.2','2008.05.31'union all
select '5439','B07040','散热片','1.2','2009.02.27'union all
select '568','C05050','异形铝','21','2007.12.28'union all
select '707','C05050','异形铝','21','2008.01.03'union all
select '2496','C05050','异形铝','19','2008.04.07'union all
select '3643','C05050','异形铝','21','2009.03.04'union all
select '7167','C05050','异形铝','20','2009.07.23'union all
select '7444','C05050','异形铝','19','2009.08.11'
go

Answer 7

测试后发现上面给出的答案有一个问题，如果该商品从头到尾只有一个价格，或从头到尾只有两个价格，则该商品不能显示出来。比如，源数据如下：
IF OBJECT_ID('tb') IS NOT NULL  DROP TABLE tb
GO
CREATE TABLE tb(单号 int, 物料号 varchar(10),物料描述 varchar(10),价格 numeric(10,2) ,日期 datetime)
GO
INSERT TB
select '205','B07040','散热片','1.1','2007.12.04'union all
select '755','B07040','散热片','1.3','2008.01.10'union all
select '2475','B07040','散热片','1.6','2008.05.08'union all
select '2526','B07040','散热片','1.6','2008.05.09'union all
select '2831','B07040','散热片','1.2','2008.05.31'union all
select '5439','B07040','散热片','1.2','2009.02.27'union all
select '568','C05050','异形铝','21','2007.12.28'union all
select '707','C05050','异形铝','21','2008.01.03'union all
select '2496','C05050','异形铝','21','2008.04.07'union all
select '3643','C05050','异形铝','21','2009.03.04'union all
select '7167','C05050','异形铝','21','2009.07.23'union all
select '7444','C05050','异形铝','19','2009.08.11'
go

Answer 8

5楼，我试了下，没问题
IF OBJECT_ID('tb') IS NOT NULL  DROP TABLE tb
GO
CREATE TABLE tb(单号 int, 物料号 varchar(10),物料描述 varchar(10),价格 numeric(10,2) ,日期 datetime)
GO
INSERT TB
select '205','B07040','散热片','1.1','2007.12.04'union all
select '755','B07040','散热片','1.3','2008.01.10'union all
select '2475','B07040','散热片','1.6','2008.05.08'union all
select '2526','B07040','散热片','1.6','2008.05.09'union all
select '2831','B07040','散热片','1.2','2008.05.31'union all
select '5439','B07040','散热片','1.2','2009.02.27'union all
select '568','C05050','异形铝','21','2007.12.28'union all
select '707','C05050','异形铝','21','2008.01.03'union all
select '2496','C05050','异形铝','19','2008.04.07'union all
select '3643','C05050','异形铝','21','2009.03.04'union all
select '7167','C05050','异形铝','20','2009.07.23'union all
select '7444','C05050','异形铝','19','2009.08.11'
goalter table tb add id int
go
declare @n int,@m decimal(10,1),@s varchar(6)
set @n=0
set @m=0
set @s=''update tb set @n=case when 价格=@m and 物料号=@s then @n+1 else 1 end,@m=价格,@s=物料号,id=@nselect 
* into #t
 from tb t
where id=1 and (select count(*) from tb where 物料号=t.物料号 and id=1 and 日期>t.日期)<2select 
    物料号 ,
    物料描述,
    最后一次价格=(select top 1 价格 from #t where 物料号=t.物料号 order by 日期 desc ),
    最后一次时间=max(日期),
    最后一次价格=(select top 1 价格 from #t where 物料号=t.物料号 order by 日期 asc ),
    最后一次时间=min(日期)
from #t t
group by 物料号 , 物料描述物料号        物料描述       最后一次价格                                  最后一次时间                  最后一次价格                                  最后一次时间
---------- ---------- --------------------------------------- ----------------------- --------------------------------------- -----------------------
B07040     散热片        1.20                                    2008-05-31 00:00:00.000 1.60                                    2008-05-08 00:00:00.000
C05050     异形铝        19.00                                   2009-08-11 00:00:00.000 20.00                                   2009-07-23 00:00:00.000(2 行受影响)
drop table #t

Answer 9

CREATE TABLE tb(单号 int, 物料号 varchar(10),物料描述 varchar(10),价格 numeric(10,2) ,日期 datetime)
GO
INSERT TB
select '205','B07040','散热片','1.1','2007.12.04'union all
select '755','B07040','散热片','1.3','2008.01.10'union all
select '2475','B07040','散热片','1.6','2008.05.08'union all
select '2526','B07040','散热片','1.6','2008.05.09'union all
select '2831','B07040','散热片','1.3','2008.05.31'union all
select '5439','B07040','散热片','1.2','2009.02.27'union all
select '568','C05050','异形铝','21','2007.12.28'union all
select '707','C05050','异形铝','21','2008.01.03'union all
select '2496','C05050','异形铝','19','2008.04.07'union all
select '3643','C05050','异形铝','21','2009.03.04'union all
select '7167','C05050','异形铝','20','2009.07.23'union all
select '7444','C05050','异形铝','19','2009.08.11'select 物料号,物料描述,价格,min(日期) as min日期,max(日期) as Max日期
into #TempTB1
from TB 
group by 物料号,物料描述,价格select 物料号,物料描述,max(case [Index] when 1 then 价格 else -1 end )  as '最后一次价格',
max(case [Index] when 1 then min日期 end ) as '最后一次价格最早出现时间',
max(case [Index] when 2 then 价格 else -1 end )  as '倒数第二次价格',
min(case [Index] when 2 then min日期 end) as '倒数第二次价格最早出现时间' from 
(
select *,(select count(0) from #TempTB1 b where b.Max日期>=a.Max日期 and b.物料号=a.物料号) as [Index]  from #TempTB1 a
)X
where [Index] in (1,2)
group by 物料号,物料描述drop table #TempTb1
drop table TB
物料号        物料描述       最后一次价格                                  最后一次价格最早出现时间            倒数第二次价格                                 倒数第二次价格最早出现时间
---------- ---------- --------------------------------------- ----------------------- --------------------------------------- -----------------------
B07040     散热片        1.20                                    2009-02-27 00:00:00.000 1.30                                    2008-01-10 00:00:00.000
C05050     异形铝        19.00                                   2008-04-07 00:00:00.000 20.00                                   2009-07-23 00:00:00.000