有个表 Temp :数据如下
Mat_no Price CDate
1001 10 2011-08-01
1001 20 2011-09-01
1001 15 2011-02-01
1002 10 2011-07-11
1002 20 2011-09-02
1002 15 2011-03-12想得到物料最近月份的那个价格,即结果是:
1001 20 2011-09-01
1002 20 2011-09-02SQL 语句如何写?
Mat_no Price CDate
1001 10 2011-08-01
1001 20 2011-09-01
1001 15 2011-02-01
1002 10 2011-07-11
1002 20 2011-09-02
1002 15 2011-03-12想得到物料最近月份的那个价格,即结果是:
1001 20 2011-09-01
1002 20 2011-09-02SQL 语句如何写?
insert into temp select 1001,10,'2011-08-01'
insert into temp select 1001,20,'2011-09-01'
insert into temp select 1001,15,'2011-02-01'
insert into temp select 1002,10,'2011-07-11'
insert into temp select 1002,20,'2011-09-02'
insert into temp select 1002,15,'2011-03-12'
go
select * from temp a where not exists(select 1 from temp where Mat_no=a.Mat_no and CDate>a.CDate)
/*
Mat_no Price CDate
----------- ----------- -----------------------
1001 20 2011-09-01 00:00:00.000
1002 20 2011-09-02 00:00:00.000(2 行受影响)*/
go
drop table temp
not exists(select 1 from temp where Mat_no=t.Mat_no and CDate>t.CDate)