找出商品在最近2次价格第一次出现的料号、描述、价格、日期

Question

源数据如下：
单号 物料号 物料描述 价格 日期
205 B07040 散热片 1.1 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.3 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 异形铝 21 2009.03.04
7167 C05050 异形铝 20 2009.07.23
7444 C05050 异形铝 19 2009.08.11希望得到的结果如下：
料号 描述 最后一次价格 最后一次时间 倒数第二次价格 倒数第二次时间
B07040 散热片   1.2       2009.02.27           1.3            2008.05.31
C05050 异形铝    19      2009.07.23            21            2009.03.04

Answer 1

;WITH MU AS (
SELECT *,ROW_NUMBER() OVER(ORDER BY 日期 DESC) AS NID
FROM TB
)
SELECT T1.物料号,T1.物料描述
,T1.价格 AS 最后一次价格
,T1.日期 AS 最后一次时间
,T2.价格 AS 倒数第二次价格
,T2.日期 AS 倒数第二次时间
FROM (
SELECT * FROM MU WHERE NID=1
) T1
LEFT JOIN (
SELECT * FROM MU WHERE NID=2
) T2 ON T1.物料号=T2.物料号

Answer 2

少写了最关键的分组，用这个：
;WITH MU AS (
SELECT *,ROW_NUMBER() OVER(GROUP BY 物料号,ORDER BY 日期 DESC) AS NID
FROM TB
)
SELECT T1.物料号,T1.物料描述
,T1.价格 AS 最后一次价格
,T1.日期 AS 最后一次时间
,T2.价格 AS 倒数第二次价格
,T2.日期 AS 倒数第二次时间
FROM (
SELECT * FROM MU WHERE NID=1
) T1
LEFT JOIN (
SELECT * FROM MU WHERE NID=2
) T2 ON T1.物料号=T2.物料号

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.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 物料号, 物料描述, 
最后一次价格 =max(case when rn=1 then 价格 end),
最后一次时间 =max(case when rn=1 then 日期 end),
倒数第二次价格 =max(case when rn=2 then 价格 end),
倒数第二次时间 =max(case when rn=2 then 日期 end)
from(
select rn=(select count(1) from tb where 物料号=t.物料号 and 日期>=t.日期),*
from tb t)t
group by 物料号, 物料描述
--结果
/*
物料号        物料描述       最后一次价格                                  最后一次时间                  倒数第二次价格                                 倒数第二次时间
---------- ---------- --------------------------------------- ----------------------- --------------------------------------- -----------------------
B07040     散热片        1.20                                    2009-02-27 00:00:00.000 1.30                                    2008-05-31 00:00:00.000
C05050     异形铝        19.00                                   2009-08-11 00:00:00.000 20.00                                   2009-07-23 00:00:00.000*/

Answer 4

--无耻的抄袭永生哥的数据
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.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';WITH MU AS (
SELECT *,ROW_NUMBER() OVER(PARTITION BY 物料号 ORDER BY 日期 DESC) AS NID
FROM TB
)
SELECT T1.物料号,T1.物料描述
,T1.价格 AS 最后一次价格
,T1.日期 AS 最后一次时间
,T2.价格 AS 倒数第二次价格
,T2.日期 AS 倒数第二次时间
FROM (
SELECT * FROM MU WHERE NID=1
) T1
LEFT JOIN (
SELECT * FROM MU WHERE NID=2
) T2 ON T1.物料号=T2.物料号
/*
B07040 散热片 1.20 2009-02-27 00:00:00.000 1.30 2008-05-31 00:00:00.000
C05050 异形铝 19.00 2009-08-11 00:00:00.000 20.00 2009-07-23 00:00:00.000
*/

Answer 5

create table #T
(
  单号  nvarchar(20),
  物料号 nvarchar(20),
  物料描述 nvarchar(10),
  价格 decimal(19,1),
  日期 datetime
)
insert into #T select '205','B07040','散热片',1.1,'2007.12.04'
union all
select '755','B07040','散热片',1.3,'2008.01.10'
union all
select '2831','B07040','散热片',1.3,'2008.05.31'
union all
select '5439','B07040','散热片',1.3,'2009.02.27'
union all
select '755','C05050','异形铝',21,'2008.01.10'
union all
select '755','C05050','异形铝',21,'2008.05.31'
union all
select '755','C05050','异形铝',20,'2009.07.23'
union all
select '755','C05050','异形铝',19,'2009.08.11'select 物料号,
       max(case when row=1 then 价格 else 0 end) '最后一次价格',
       max(case when row=1 then 日期 else 0 end) '最后一次日期',
       max(case when row=2 then 价格 else 0 end) '最后一次价格',
       max(case when row=2 then 日期 else 0 end) '最后一次日期'
from 
(
select *,
       (select count(*)+1 from #T where 物料号=T.物料号 and 日期>T.日期) row
from #T T
) TT
group by 物料号物料号                  最后一次价格                                  最后一次日期                  最后一次价格                                  最后一次日期
-------------------- --------------------------------------- ----------------------- --------------------------------------- -----------------------
B07040               1.3                                     2009-02-27 00:00:00.000 1.3                                     2008-05-31 00:00:00.000
C05050               19.0                                    2009-08-11 00:00:00.000 20.0                                    2009-07-23 00:00:00.000(2 行受影响)

Answer 6

IF OBJECT_ID('T') IS NOT NULL  DROP TABLE T
GO
CREATE TABLE T(单号 int, 物料号 varchar(10),物料描述 varchar(10),价格 numeric(10,2) ,日期 datetime)
GO
INSERT T
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 物料号,物料描述,
       max(case when row=1 then 价格 else 0 end) '最后一次价格',
       max(case when row=1 then 日期 else 0 end) '最后一次日期',
       max(case when row=2 then 价格 else 0 end) '倒数第二次价格',
       max(case when row=2 then 日期 else 0 end) '倒数第二次日期'
from 
(
select *,
       (select count(*)+1 from T where 物料号=T1.物料号 and 日期>T1.日期) row
from T T1
) TT
group by 物料号,物料描述物料号        物料描述       最后一次价格                                  最后一次日期                  倒数第二次价格                                 倒数第二次日期
---------- ---------- --------------------------------------- ----------------------- --------------------------------------- -----------------------
B07040     散热片        1.20                                    2009-02-27 00:00:00.000 1.30                                    2008-05-31 00:00:00.000
C05050     异形铝        19.00                                   2009-08-11 00:00:00.000 20.00                                   2009-07-23 00:00:00.000(2 行受影响)

Answer 7

重新把源数据和希望的结果整理了一下，请高手帮忙看看源数据如下：
单号 物料号 物料描述 价格 日期
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 8

源数据如下：
单号 物料号 物料描述 价格 日期
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 9

--建立测试环境
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 10

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 11

select * from tb mt where 单号 in (
select top 2 单号 from tb where mt.物料描述=物料描述 order by 日期 desc
)