如何用SQL语句查出一个表内所有编号唯一的最近一时间内所有记录？

[BarryW]

如我有一个表（TEST）有如下记录：
PS_DD PRD_NO UP
2003-12-1 1000009301 0
2004-3-15 1000009301 12
2004-3-15 1000009301 1.25
2004-3-11 8800233090 3
2004-3-13 8800233090 0
2004-3-9 1000009420 34
2004-3-10 1000009420 1
2004-3-15 1000009421 4
2004-3-12 1000009428 7

编号(PRD_NO)1000009301 在上表中发生过三次交易，日期（PS_DD）分别为（2003-12-1，2004-3-15,2004-3-15）；2004-3-15号是1000009301与现在最近的日期，所以我要把这个编号的2004-3-15的记录找出来；

同理编号(PRD_NO)8800233090在上表中发生过两次交易日期（PS_DD）分别为（2003-12-11，2004-3-13）；2004-3-13号是8800233090与现在最近的日期，所以我要把这个编号的2004-3-13的记录找出来；

2004-3-10是1000009420与现在最近的日期，所以我要把这个编号的2004-3-10的记录找出来；

而1000009420,1000009428是单笔的记录也就默认是最新的记录，也把它查找出来；

由上组查找出一个新的表QTEST：
PS_DD PRD_NO UP
2004-3-15 1000009301 12
2004-3-15 1000009301 1.25
2004-3-13 8800233090 0
2004-3-10 1000009420 1
2004-3-15 1000009421 4
2004-3-12 1000009428 7

由TEST表查找出以上的一个表QTEST应如何做？

谢谢！

[polestarxu]

select max(PS_DD),PRD_NO,UP
from test
试试看行不行

[愉快的登山者]

select * from TEST as A
where ps_dd = (select max(ps_dd) from test where prd_no = A.prd_no)

[zjcxc]

select a.*
from TEST A join(
select prd_no,ps_dd=max(ps_dd) from test group by prd_no
)b on a.ps_dd=b.ps_dd and a.prd_no = b.prd_no

[victorycyz]

select a.*
from test a join
( select max(ps_dd) as maxdd,prd_no
from test
group by prd_no
) b
on a.ps_dd=b.maxdd and a.prd_no=b.prd_no

[LoveSQL]

下面是个测试的例子
declare @ table (PS_DD datetime,PRD_NO varchar(20),UP varchar(10))

insert @ select

'2003-12-1', '1000009301' ,'0' union select
'2004-3-15', '1000009301' ,'12' union select
'2004-3-15', '1000009301' ,'1.25' union select
'2004-3-11', '8800233090' ,'3' union select
'2004-3-13', '8800233090' ,'0' union select
'2004-3-9' , '1000009420' ,'34' union select
'2004-3-10', '1000009420' ,'1' union select
'2004-3-15', '1000009421' ,'4' union select
'2004-3-12', '1000009428' ,'7'


select * from @ a,(select max(PS_DD) as PS_DD,PRD_NO from @ group by PRD_NO)b
where a.PS_DD=b.PS_DD and a.PRD_NO=b.PRD_NO
order by a.PS_DD

结果
PS_DD PRD_NO UP PS_DD PRD_NO
------------------------------------------------------ -------------------- ---------- ------------------------------------------------------ --------------------
2004-03-10 00:00:00.000 1000009420 1 2004-03-10 00:00:00.000 1000009420
2004-03-12 00:00:00.000 1000009428 7 2004-03-12 00:00:00.000 1000009428
2004-03-13 00:00:00.000 8800233090 0 2004-03-13 00:00:00.000 8800233090
2004-03-15 00:00:00.000 1000009421 4 2004-03-15 00:00:00.000 1000009421
2004-03-15 00:00:00.000 1000009301 1.25 2004-03-15 00:00:00.000 1000009301
2004-03-15 00:00:00.000 1000009301 12 2004-03-15 00:00:00.000 1000009301

(6 row(s) affected)

[leeboyan]

select a.* into QTEST from TEST a,(select max(PS_DD)as PS_DD,PRD_NO from TEST group by PRD_NO) b where a.PS_DD=b.PS_DD and a.PRD_NO=b.PRD_NO

[LoveSQL]

select a.* from @ a,(select max(PS_DD) as PS_DD,PRD_NO from @ group by PRD_NO)b
where a.PS_DD=b.PS_DD and a.PRD_NO=b.PRD_NO
order by a.PS_DD

结果
PS_DD PRD_NO UP
------------------------------------------------------ -------------------- ----------
2004-03-10 00:00:00.000 1000009420 1
2004-03-12 00:00:00.000 1000009428 7
2004-03-13 00:00:00.000 8800233090 0
2004-03-15 00:00:00.000 1000009421 4
2004-03-15 00:00:00.000 1000009301 1.25
2004-03-15 00:00:00.000 1000009301 12

(6 row(s) affected)

[BarryW]

如果是同一天的同一物料不同的价格就任取一个作为最后价格我又如何做呢？
如上面1000009301在2004-3-15 有两笔单价:
2004-3-15 1000009301 12
2004-3-15 1000009301 1.25

我查找出来只要其中的一笔:
PS_DD PRD_NO UP
2004-3-15 1000009301 12
2004-3-13 8800233090 0
2004-3-10 1000009420 1
2004-3-15 1000009421 4
2004-3-12 1000009428 7
或者：
PS_DD PRD_NO UP
2004-3-15 1000009301 1.25
2004-3-13 8800233090 0
2004-3-10 1000009420 1
2004-3-15 1000009421 4
2004-3-12 1000009428 7

这样我又如何做呢？

[LoveSQL]

--那就这样就行了
declare @ table (PS_DD datetime,PRD_NO varchar(20),UP varchar(10))

insert @ select

'2003-12-1', '1000009301' ,'0' union select
'2004-3-15', '1000009301' ,'12' union select
'2004-3-15', '1000009301' ,'1.25' union select
'2004-3-11', '8800233090' ,'3' union select
'2004-3-13', '8800233090' ,'0' union select
'2004-3-9' , '1000009420' ,'34' union select
'2004-3-10', '1000009420' ,'1' union select
'2004-3-15', '1000009421' ,'4' union select
'2004-3-12', '1000009428' ,'7'


select PS_DD,PRD_NO,max(UP) as UP from (
select a.* from @ a,(select max(PS_DD) as PS_DD,PRD_NO from @ group by PRD_NO)b
where a.PS_DD=b.PS_DD and a.PRD_NO=b.PRD_NO

)a
group by PS_DD,PRD_NO
order by a.PS_DD
--结果
PS_DD PRD_NO UP
------------------------------------------------------ -------------------- ----------
2004-03-10 00:00:00.000 1000009420 1
2004-03-12 00:00:00.000 1000009428 7
2004-03-13 00:00:00.000 8800233090 0
2004-03-15 00:00:00.000 1000009301 12
2004-03-15 00:00:00.000 1000009421 4

(5 row(s) affected)

[BarryW]

max(UP) as UP

上兄，你取的是最高单价！

我要的不是最高的单价！

“如果是同一天的同一物料不同的价格就任取一个作为最后价格我又如何做呢？”