使用GROUP BY / MAX获取相邻字段

Question

这个问题可能已经被问过很多次了，但我找不到解决办法，因为我不知道如何表达这个问题。

[product] [shop]  [price] [date]
Pizza     Shop1   10      2014-05-10
Pizza     Shop2   12      2014-05-04
Snow      Shop1   101     2014-05-02
Snow      Shop3   93      2014-05-11

我想查询这张表并得到最后增加的产品的价格:

[product] [shop]  [price] [date]
Pizza     Shop1   10      2014-05-10
Snow      Shop3   93      2014-05-11

明显错误的语法:

SELECT
  product,
  shop WHERE MAX(date),
  price WHERE MAX(date),
  MAX(date)
FROM myTable
GROUP BY product

这个查询已经是子查询的一部分，所以我想要最好的执行解决方案。

Answer 1

使用ROW_NUMBER()，您可以将记录拆分为每个产品的分区，并为每个分区分配一个行号，从1开始表示分区(产品)的最新记录。

然后为每个产品选择行号为1的所有记录。

WITH
  sequenced AS
(
  SELECT
    myTable.*,
    ROW_NUMBER() OVER (PARTITION BY [product] ORDER BY [date] DESC)  AS product_date_ordinal
  FROM
    myTable
)
SELECT
  *
FROM
  sequenced
WHERE
  product_date_ordinal = 1

这里假设SQL SERVER 2005以后，您应该在product, date DESC上有一个索引，以获得最佳性能。

Answer 2

您也可以试试这个。使用连接子查询来过滤您的表，我猜不如第一个答案好，但更容易阅读和理解:

SELECT Table.*
FROM Table
    RIGHT JOIN
    (
        SELECT product, max(date) AS date FROM Table
        GROUP BY product
    ) AS Filter
    ON Filter.product = Table.product AND Filter.date = Table.date

只需将"Table"替换为您的表名。