从表中取得最近2次采购价格的语句

Question

公司采购需要分析价格变动，需要分析最近的2次采购价格，即最近的2次采购价格，并且分2列显示，
源表如下
物料号 描述 价格 采购时间
20-0AB0 主机零件 0.47 2010/7/30
20-0AB0 主机零件 0.46 2010/7/18
20-0AB0 主机零件 0.46 2010/7/9
20-0AB0 主机零件 0.45 2010/7/1
20-0AB0 主机零件 0.45 2010/6/21
0B-1030 硬盘 350 2010/7/29
0B-1031 硬盘 370 2010/7/3
0B-1032 硬盘 345 2010/6/24
19-QUEN 光电鼠标 25 2010/5/18
20-QUEN 光电鼠标 35 2010/4/26
21-QUEN 光电鼠标 40 2010/4/2
22-QUEN 光电鼠标 40 2010/3/28要求如下结果
物料号    描述    价格一   价格二
20-0AB0 主机零件 0.47 0.46
0B-1030 硬盘 350 370
19-QUEN 光电鼠标 25 35

Answer 1

with a as(
select *,row_number() over(order by 采购时间 desc) rn
from 表
)b as(
select * from a where rn in(1,2))
select 物料号, 描述,case rn when 1 then 价格 end  价格一 ,
case rn when 2 then 价格 end  价格二 
from b group by 物料号, 描述

Answer 2

with a as( select *,row_number() over(order by 采购时间 desc) rn from 表 ),
b as( select * from a where rn in(1,2)) 
select 物料号, 描述,case rn when 1 then 价格 end 价格一 , case rn when 2 then 价格 end 价格二 from b group by 物料号, 描述

Answer 3

if object_id('tb') is not null
drop table tb
go
create table tb (物料号 varchar(10),描述 varchar(10),价格 numeric(10,2),采购时间 date)
insert into tb
select '20-0AB0', '主机零件', 0.47, '2010/7/30' union all
select '20-0AB0', '主机零件', 0.46, '2010/7/18' union all
select '20-0AB0', '主机零件', 0.46, '2010/7/9' union all
select '20-0AB0', '主机零件', 0.45, '2010/7/1' union all
select '20-0AB0', '主机零件', 0.45, '2010/6/21' union all
select '0B-1030', '硬盘', 350, '2010/7/29' union all
select '0B-1031', '硬盘', 370, '2010/7/3' union all
select '0B-1032', '硬盘', 345, '2010/6/24' union all
select '19-QUEN', '光电鼠标', 25, '2010/5/18' union all
select '20-QUEN', '光电鼠标', 35, '2010/4/26' union all
select '21-QUEN', '光电鼠标', 40, '2010/4/2' union all
select '22-QUEN', '光电鼠标', 40, '2010/3/28';with t as
(
select rn=ROW_NUMBER() over(partition by 描述 order by 采购时间 desc) ,*
from tb
)
select (select 物料号 from t where 描述=A.描述 and rn=1) 物料号, 描述,
        价格一=SUM( case rn when 1 then 价格 else 0 end),
        价格二=SUM( case rn when 2 then 价格 else 0 end)
from t a
group by 描述物料号 描述 价格一 价格二
19-QUEN 光电鼠标 25.00 35.00
0B-1030 硬盘 350.00 370.00
20-0AB0 主机零件 0.47 0.46

Answer 4

--建立测试环境
IF OBJECT_ID('tb') IS NOT NULL  DROP TABLE tb
GO
CREATE TABLE tb ( 物料号  varchar(20), 描述  varchar(20), 价格  numeric(10,2), 采购时间 datetime)
GO
Insert tb  
select '20-0AB0','主机零件','0.47','2010/7/30'
union all select '20-0AB0','主机零件','0.46','2010/7/18'
union all select '20-0AB0','主机零件','0.46','2010/7/9'
union all select '20-0AB0','主机零件','0.45','2010/7/1'
union all select '20-0AB0','主机零件','0.45','2010/6/21'
union all select '0B-1030','硬盘','350','2010/7/29'
union all select '0B-1031','硬盘','370','2010/7/3'
union all select '0B-1032','硬盘','345','2010/6/24'
union all select '19-QUEN','光电鼠标','25','2010/5/18'
union all select '20-QUEN','光电鼠标','35','2010/4/26'
union all select '21-QUEN','光电鼠标','40','2010/4/2'
union all select '22-QUEN','光电鼠标','40','2010/3/28'
go
--查询
select min(物料号)物料号,描述,
价格一=max(case when rn=1 then 价格 end),
价格二=max(case when rn=2 then 价格 end)
from(
select * ,rn=(select count(1) +1 from tb where 描述=t.描述 and 采购时间>t.采购时间) 
from tb t 
) t
group by 描述
--结果
/*
物料号                  描述                   价格一                                     价格二
-------------------- -------------------- --------------------------------------- ---------------------------------------
19-QUEN              光电鼠标                 25.00                                   35.00
0B-1030              硬盘                   350.00                                  370.00
20-0AB0              主机零件                 0.47                                    0.46
*/

Answer 5

jaydom的方案可行，不过效率有问题哦，我运行了3分钟还没运行完，是否有更优的算法？

Answer 6

xys_777 的方案可行，不过还没看懂，呵呵

Answer 7

--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (物料号 varchar(7),描述 varchar(8),价格 numeric(8,2),采购时间 datetime)
insert into [tb]
select '20-0AB0','主机零件',0.47,'2010/7/30' union all
select '20-0AB0','主机零件',0.46,'2010/7/18' union all
select '20-0AB0','主机零件',0.46,'2010/7/9' union all
select '20-0AB0','主机零件',0.45,'2010/7/1' union all
select '20-0AB0','主机零件',0.45,'2010/6/21' union all
select '0B-1030','硬盘',350,'2010/7/29' union all
select '0B-1031','硬盘',370,'2010/7/3' union all
select '0B-1032','硬盘',345,'2010/6/24' union all
select '19-QUEN','光电鼠标',25,'2010/5/18' union all
select '20-QUEN','光电鼠标',35,'2010/4/26' union all
select '21-QUEN','光电鼠标',40,'2010/4/2' union all
select '22-QUEN','光电鼠标',40,'2010/3/28'
goselect 物料号=min(物料号),描述,价格一=max(case px when 1 then 价格 else 0 end),
价格二=max(case px when 12 then 价格 else 0 end)
from
(select *,px=row_number() over(partition by 描述 order by 采购时间 desc) from [tb])a
group by 描述/*结果：
物料号     描述       价格一                                     价格二
------- -------- --------------------------------------- ---------------------------------------
19-QUEN 光电鼠标     25.00                                   0.00
0B-1030 硬盘       350.00                                  0.00
20-0AB0 主机零件     0.47                                    0.00
*/

Answer 8

可以结贴了，已经用上啦
用的是pt1314917的方案xys_777 的方案也是正确的，但是如果有两行记录的时间相同的话，则还是有问题，这时候需要根据编号来排序了。因为我的示例中没有给出编号字段，所以你的方案是正确的。再次感谢各位的回复！