SQL查询最近记录

Question

下午好，

我整天都在做这件事，我无法弄清楚。我正在用Excel执行此查询。

SELECT 
PRICE_BOOK.status,
PRICE_BOOK.ms_code,
PRICE_BOOK.location_code,
SUPPLIER_LOC_HDR.name,
PRICE_BOOK.mfg_code,
PRICE_BOOK.group_code,
PRICE_BOOK.stock_id,
PRICE_BOOK.effective_date,
PRICE_BOOK.price
FROM dbo.MS_INFO MS_INFO, dbo.PRICE_BOOK PRICE_BOOK, dbo.SUPPLIER_LOC_HDR 
SUPPLIER_LOC_HDR
WHERE MS_INFO.ms_code = PRICE_BOOK.ms_code AND 
SUPPLIER_LOC_HDR.location_code = PRICE_BOOK.location_code AND 
((PRICE_BOOK.mfg_code Not In ('Type1','Type2','Type3'))) 

这正在导出每个位置，产品，生效时间和价格的价格。

但是，我只需要ms_code，location_code，name，mfg_code，group_code，stock_id。

换句话说，我只需要每个位置的最新价格。

status ms_code location_code name mfg_code group_code stock_id effective_date price A Supplier1 Terminal 1 City #1 PartType1 Group1 61 7/5/17 0:01 1.09 A Supplier1 Terminal 1 City #1 PartType1 Group1 61 7/4/17 0:01 1.41 A Supplier1 Terminal 1 City #2 PartType1 Group1 61 7/3/17 0:01 1.76 A Supplier1 Terminal 1 City #2 PartType1 Group1 61 5/24/17 0:01 1.20 A Supplier1 Terminal 1 City #1 PartType1 Group1 62 7/5/17 0:01 1.67 A Supplier1 Terminal 1 City #1 PartType1 Group1 62 7/4/17 0:01 1.19 A Supplier1 Terminal 1 City #1 PartType1 Group1 62 7/3/17 0:01 1.14 A Supplier1 Terminal 1 City #1 PartType1 Group1 62 5/24/17 0:01 1.11 A Supplier1 Terminal 1 City #1 PartType1 Group1 63 7/5/17 0:01 1.33 A Supplier1 Terminal 1 City #1 PartType1 Group1 63 7/4/17 0:01 1.59 A Supplier1 Terminal 1 City #1 PartType1 Group1 63 7/3/17 0:01 1.61 A Supplier1 Terminal 1 City #1 PartType1 Group1 63 5/24/17 0:01 1.75 A Supplier1 Terminal 1 City #1 PartType1 Group1 64 7/5/17 0:01 1.75 A Supplier1 Terminal 1 City #1 PartType1 Group1 64 7/4/17 0:01 1.77 A Supplier2 Terminal 1 City #1 PartType1 Group1 64 7/3/17 0:01 1.45 A Supplier2 Terminal 1 City #1 PartType1 Group1 64 5/24/17 0:01 1.77

预期结果

status ms_code location_code name mfg_code group_code stock_id effective_date price A Supplier1 Terminal 1 City #1 PartType1 Group1 61 7/5/17 0:01 1.09 A Supplier1 Terminal 1 City #2 PartType1 Group1 61 7/3/17 0:01 1.76 A Supplier1 Terminal 1 City #1 PartType1 Group1 62 7/5/17 0:01 1.67 A Supplier1 Terminal 1 City #1 PartType1 Group1 63 7/5/17 0:01 1.33 A Supplier1 Terminal 1 City #1 PartType1 Group1 64 7/5/17 0:01 1.75 A Supplier2 Terminal 1 City #1 PartType1 Group1 64 7/3/17 0:01 1.45

编辑：每个时钟的评论更改查询

SELECT
PRICE_BOOK.status,
PRICE_BOOK.ms_code,
PRICE_BOOK.location_code,
SUPPLIER_LOC_HDR.name,
PRICE_BOOK.mfg_code,
PRICE_BOOK.group_code,
PRICE_BOOK.stock_id,
PRICE_BOOK.effective_date,
PRICE_BOOK.price
FROM dbo.PRICE_BOOK PRICE_BOOK
    INNER JOIN dbo.SUPPLIER_LOC_HDR SUPPLIER_LOC_HDR
        ON SUPPLIER_LOC_HDR.location_code = PRICE_BOOK.location_code
WHERE ((PRICE_BOOK.mfg_code Not In ('Type1','Type2','Type3')))

预先感谢

jeff

Answer 1

您应该使用窗口函数。这样的东西：

qualify row_number() over(partition by product, location order by effective_date desc) = 1