sql - 只选择最新的记录

Question

这里有人知道吗？

select distinct
    PRODID as PRODUCT_ID,
    STRCDE as STORECODE,
    PRCPR as Price,
    STDTE as Date
from [DWSTAGE].[PRCPLN]
order by PRODID 

查询结果示例

PRODUCT_ID    | STORECODE    | Price  |   Date
---------------------------------------------------
15707         |  15007       |   11    | 2012-01-03
15707         |  15007       |   13    | 2012-02-03
22102         |  15001       |   12    | 2012-01-02
40377         |  15002       |   13    | 2012-01-04
40377         |  15003       |   14    | 2012-02-03
40377         |  15003       |   15    | 2012-03-01
44775         |  15001       |   16    | 2012-06-01

我的预期结果

PRODUCT_ID    | STORECODE    | Price  |   Date
---------------------------------------------------
15707         |  15007       |   13    | 2012-02-03
22102         |  15001       |   12    | 2012-01-02
40377         |  15002       |   13    | 2012-01-04
40377         |  15003       |   15    | 2012-03-01
44775         |  15001       |   16    | 2012-06-01

标签： sqlsql-server

Answer 1

使用not exists如下：

select distinct PRODID as PRODUCT_ID, STRCDE as STORECODE, PRCPR as Price, STDTE as Date
From [DWSTAGE].[PRCPLN] t
Where not exists
(Select 1 from [DWSTAGE].[PRCPLN] tt
 Where tt.PRODID = t.PRODID
   And tt.PRODUCT_ID = t.PRODUCT_ID
   And tt.STDTE > t.STDTE)
order by PRODID