求助：进货单价，最近2次价格比对

[风寒晓]

在日常工作，常常会有这样的例子：

create table HJ (单别 CHAR(4),单号 CHAR(11),序号 CHAR(4),料号 CHAR(20),日期 CHAR(8),单价 NUMERIC(16,4) )

INSERT INTO HJ VALUES('A1','20120100001','0001','钢笔','20120101','100')
INSERT INTO HJ VALUES('A1','20120100002','0001','铅笔','20120101','100')
INSERT INTO HJ VALUES('A1','20120100003','0001','圆珠笔','20120101','100')

INSERT INTO HJ VALUES('A1','20120200001','0001','钢笔','20120201','90')
INSERT INTO HJ VALUES('A1','20120200002','0001','铅笔','20120201','96')
INSERT INTO HJ VALUES('A1','20120200003','0001','圆珠笔','20120201','106')

INSERT INTO HJ VALUES('A1','20120300001','0001','钢笔','20120301','91')
INSERT INTO HJ VALUES('A1','20120300002','0001','铅笔','20120301','99')
INSERT INTO HJ VALUES('A1','20120300003','0001','圆珠笔','20120301','101')

SELECT * FROM HJ

--
A1 20120100001 0001 钢笔 20120101 100.0000
A1 20120100002 0001 铅笔 20120101 100.0000
A1 20120100003 0001 圆珠笔 20120101 100.0000
A1 20120200001 0001 钢笔 20120201 90.0000
A1 20120200002 0001 铅笔 20120201 96.0000
A1 20120200003 0001 圆珠笔 20120201 106.0000
A1 20120300001 0001 钢笔 20120301 91.0000
A1 20120300002 0001 铅笔 20120301 99.0000
A1 20120300003 0001 圆珠笔 20120301 101.0000
--现在想通过一条查询语句，查询出同一个料号最近2次的进货价格，然后再比对差异。用TOP不行，因为TOP是取全部的排序值，用排序函数ROW-NUMBER也不行，因为也是全部的。这个例子最困难的地方在于，是取每种料号的最近2次价格。在ERP浩瀚的数据当中，如何达到这样的效果？
--目前我的思路是 先MAX（日期），再用剩下的日期再MAX(日期)，似乎太复杂。。。

高手们，支个招！

[开着拖拉机泡妞]

create table HJ (单别 CHAR(4),单号 CHAR(11),序号 CHAR(4),料号 CHAR(20),日期 CHAR(8),单价 NUMERIC(16,4) )

INSERT INTO HJ VALUES('A1','20120100001','0001','钢笔','20120101','100')
INSERT INTO HJ VALUES('A1','20120100002','0001','铅笔','20120101','100')
INSERT INTO HJ VALUES('A1','20120100003','0001','圆珠笔','20120101','100')

INSERT INTO HJ VALUES('A1','20120200001','0001','钢笔','20120201','90')
INSERT INTO HJ VALUES('A1','20120200002','0001','铅笔','20120201','96')
INSERT INTO HJ VALUES('A1','20120200003','0001','圆珠笔','20120201','106')

INSERT INTO HJ VALUES('A1','20120300001','0001','钢笔','20120301','91')
INSERT INTO HJ VALUES('A1','20120300002','0001','铅笔','20120301','99')
INSERT INTO HJ VALUES('A1','20120300003','0001','圆珠笔','20120301','101')

select 单别,单号,序号,料号 ,日期,单价 from(
select px=ROW_NUMBER()over(partition by 料号 order by 日期 desc),
* from HJ)t
where px<=2

/*
单别	单号	序号	料号	日期	单价
A1  	20120300001	0001	钢笔                	20120301	91.0000
A1  	20120200001	0001	钢笔                	20120201	90.0000
A1  	20120300002	0001	铅笔                	20120301	99.0000
A1  	20120200002	0001	铅笔                	20120201	96.0000
A1  	20120300003	0001	圆珠笔              	20120301	101.0000
A1  	20120200003	0001	圆珠笔              	20120201	106.0000
*/

row_number是可以的

[xxjltan]

你的思路用max()日期不实际，如果同一天一种商品购了好几次哪，这就不能分开了。

[tyhjx]

[Quote=引用 1 楼 的回复:]
楼主说的同日期多单问题，看看这样修改下如何？

select 单别,单号,序号,料号 ,日期,单价 from(
select px=ROW_NUMBER()over(partition by 料号 order by 日期 desc),
* from HJ)t

[/Quote]

++

楼主说的同日期多单问题，看看这样修改下如何？
[code=SQL]select 单别,单号,序号,料号 ,日期,单价 from(
select px=ROW_NUMBER()over(partition by 料号 order by 单号 desc),
* from HJ)t
where px<=2

[孤独加百列]

用ROW_NUMBER的1楼写了，给你写一个用TOP的

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'HJ')
BEGIN
	DROP TABLE HJ
END
GO
create table HJ (单别 CHAR(4),单号 CHAR(11),序号 CHAR(4),料号 CHAR(20),日期 CHAR(8),单价 NUMERIC(16,4) )

INSERT INTO HJ VALUES('A1','20120100001','0001','钢笔','20120101','100')
INSERT INTO HJ VALUES('A1','20120100002','0001','铅笔','20120101','100')
INSERT INTO HJ VALUES('A1','20120100003','0001','圆珠笔','20120101','100')

INSERT INTO HJ VALUES('A1','20120200001','0001','钢笔','20120201','90')
INSERT INTO HJ VALUES('A1','20120200002','0001','铅笔','20120201','96')
INSERT INTO HJ VALUES('A1','20120200003','0001','圆珠笔','20120201','106')

INSERT INTO HJ VALUES('A1','20120300001','0001','钢笔','20120301','91')
INSERT INTO HJ VALUES('A1','20120300002','0001','铅笔','20120301','99')
INSERT INTO HJ VALUES('A1','20120300003','0001','圆珠笔','20120301','101')

--SELECT * FROM HJ

SELECT 单别,单号,序号,料号,日期,单价
FROM HJ AS A
WHERE 单号 IN (SELECT TOP 2 单号 FROM HJ WHERE 料号 = A.料号 ORDER BY 日期 DESC)
ORDER BY 料号,日期 DESC

单别	单号	序号	料号	日期	单价
A1  	20120300001	0001	钢笔                	20120301	91.0000
A1  	20120200001	0001	钢笔                	20120201	90.0000
A1  	20120300002	0001	铅笔                	20120301	99.0000
A1  	20120200002	0001	铅笔                	20120201	96.0000
A1  	20120300003	0001	圆珠笔              	20120301	101.0000
A1  	20120200003	0001	圆珠笔              	20120201	106.0000

[风寒晓]

感谢大家的回复，最终如下：

create view [进货价格]    
as    
SELECT distinct PURTH.TH004 进货品号,TG005 供应商,TG010 含税,TG003 进货日,TG007 进货币种,TH018 原币单价    
FROM PURTH JOIN PURTG ON TG001=TH001 AND TG002=TH002     
WHERE TG013='Y' AND TH018<>0  AND PURTH.TH001=3401
--
create view [进货价格排名]  
as  
select  ROW_NUMBER()over(partition by 进货品号,供应商,进货币种,含税 order by 进货日 desc) 排名,进货品号,供应商,进货日,  
进货币种,含税,原币单价  from 进货价格
--
select a1.排名,a1.进货品号,a1.供应商,a1.进货日,a1.进货币种,a1.含税,a1.原币单价,a2.原币单价
from 进货价格排名 a1
left join (
select 排名,进货品号,供应商,进货日,进货币种,含税,原币单价
from 进货价格排名 where 排名=2
) a2 on a2.进货品号=a1.进货品号 and a2.供应商=a1.供应商  and a2.进货币种=a1.进货币种 and a2.含税=a1.含税
where a1.排名=1

[风寒晓]

top 试了一下，面对 “进货品号,供应商,进货币种,含税” 四个条件，有点力不从心啊