在SQL中选择最近的记录

Question

我想在SQL中选择数据集中最新的非零条目。大多数示例只返回日期和分组变量，但我也希望返回相关值。例如：

ID       Date          Value
----------------------------
001      2014-10-01     32
001      2014-10-05     10
001      2014-10-17      0
002      2014-10-03     17
002      2014-10-20     60
003      2014-09-30     90
003      2014-10-10      7
004      2014-10-06    150
005      2014-10-17      0
005      2014-10-18      9

使用

SELECT ID, MAX(Date) AS MDate FROM Table WHERE Value > 0 GROUP BY ID

返回：

ID       Date      
-------------------
001      2014-10-05
002      2014-10-20
003      2014-10-10
004      2014-10-06
005      2014-10-18

但是，每当我尝试将Value作为所选变量之一时，SQLServer会出现一个错误：

“在选择列表中，因为该列没有包含在聚合函数或GROUP BY子句中，所以列'Value'无效。”

我的期望结果是：

ID       Date          Value
----------------------------
001      2014-10-05     10
002      2014-10-20     60
003      2014-10-10      7
004      2014-10-06    150
005      2014-10-18      9

我想到的一个解决方案是，在原始表格中查找结果，返回对应于相关ID和日期（我已经将其剪裁并且我知道这些是唯一的）的值，但在我看来，这似乎是一种混乱的解决方案。如果能提供帮助，我将不胜感激。

注意：我不想按Value分组，因为这是最终要提取的结果（即对于每个ID，我都想要最新的Value）。举个例子：

ID       Date          Value
----------------------------
001      2014-10-05     10
001      2014-10-06     10
001      2014-10-10     10
001      2014-10-12      8
001      2014-10-18      0

在这里，我只需要最后一个非零条目。(001、2014-10-12、8)

SELECT ID, MAX(Date) AS MDate, Value FROM Table WHERE Value > 0 GROUP BY ID, Value

Would return:

ID       Date          Value
----------------------------
001      2014-10-10     10
001      2014-10-12      8

- Michael Barrowman

---

所有这些自连接等操作都可以通过窗口函数进行优化。SQLfiddle示例。 - Aaron Bertrand

@AaronBertrand，谢谢您的建议。在阅读这个建议之前，我已经实现了下面的重新引用代码，但如果将来需要重新编码，我会记住这个建议的。 - Michael Barrowman

Answer 1

这也可以使用窗口函数来完成，这往往比在分组查询上进行连接更快：

select id, date, value
from (
  select id,
         date,
         value,
         row_number() over (partition by id order by date desc) as rn
  from the_table
) t
where rn = 1
order by id;

- user330315

Answer 2

假设在表中，同一ID不存在重复的日期，则以下代码应该有效：

SELECT A.ID, A.Date, A.Value
FROM
   T1 AS A
   INNER JOIN (SELECT ID,MAX(Date) AS Date FROM T1 WHERE Value > 0 GROUP BY ID) AS B
      ON A.ID = B.ID AND A.Date = B.Date

- Joao Araujo

---

谢谢。看起来这应该可以工作。如果这是我问题的最简单解决方案，那我想我就得这么做了。我只是觉得可能有比这样重新引用我的原始数据集更简单的方法。 - Michael Barrowman

@Michael，使用窗口函数有一个更好的解决方案。 - Aaron Bertrand

+1 对于快速响应的编辑和有效的回答。欢迎来到 Stack Overflow，@JoaoAraujo！ - AHiggins

Answer 3

select a.id, a.date, a.value from Table1 a inner join (

select id, max(date) mydate from table1 
where Value>0 group by ID) b on a.ID=b.ID and a.Date=b.mydate

- Horaciux

Answer 4

使用子查询，

SELECT  ID, Date AS MDate, VALUE 
FROM    table  t1
where   date = (Select max(date)
                from    table t2
                where   Value >0
                and     t1.id = t2.id
                )

- arunbabu

Answer 5

提供的答案完全足够，但使用CTE：

;WITH cteTable
AS
(
  SELECT
    Table.ID [ID], MAX(Date) [MaxDate]
  FROM
    Table
  WHERE
    Table.Value > 0
  GROUP BY
    Table.ID
)

SELECT
    cteTable.ID, cteTable.Date, Table.Value
FROM
    Table INNER JOIN cteTable ON (Table.ID = cteTable.ID)

- cope