SQL按日期获取最后一项

Question

我有一个查询，我正在处理一个项目，然后我删除了数据集并重新开始，现在我根本无法获取任何数据。
查询基本上是:

SELECT * 
FROM   TABLEA 
       LEFT JOIN TABLEB 
              ON TABLEA.ID = TABLEB.ID 
WHERE  TABLEA.ID = 1 
       AND TABLEB.DATE = (SELECT MAX(TABLEB.DATE) 
                          FROM   TABLEB 
                          WHERE  TABLEA.ID = 1) 

TableB 跟踪更改并且每个 ID 有数百个条目。我想要与该 ID 相关的最后一个按时间顺序排列的单行。
如果有更好的方法来做到这一点，那就太棒了，但我真的很想知道为什么这个特定的查询不起作用。当我运行此查询时:

SELECT MAX(TABLEB.DATE) 
FROM   TABLEB 
       LEFT JOIN TABLEB 
              ON TABLEA.ID = TABLEB.ID 
WHERE  TABLEA.ID = 1 

我得到了数据集中最后一个日期的正确值。

Answer 1

select * 
from tableA as a
    left outer join tableB as b on b.ID = a.ID
where
    b.Date = (select max(t.Date) from tableB as t WHERE t.ID = a.id)
    -- and a.ID = 1 if you need it

如果你只需要 tableB 中的日期，你可以做

select * 
from tableA as a
    left outer join (
        select max(t.Date) as Date, t.ID from tableB as t group by t.ID
    ) as b on b.ID = a.ID
-- where a.ID = 1 if you need it

如果您可以使用 row_number函数(您可以将公用表表达式更改为子查询):

with cte as (
    select *, row_number() over(partition by a.ID order by b.Date desc) as rn
    from tableA as a
        left outer join tableB as b on b.ID = a.ID        
    -- where a.ID = 1 if you need it
)
select *
from cte
where rn = 1

如果您使用的是 SQL Server 版本 >= 2005:

select * 
from tableA as a
    outer apply (
        select top 1 t.*
        from tableB as t
        where t.ID = a.ID
        order by t.Date desc
    ) as b
-- where a.ID = 1 if you need it

请注意在所有子查询中使用别名。

关于您的初始查询，我认为您在那里有一个错字:

SELECT * 
FROM   TABLEA 
       LEFT JOIN TABLEB 
              ON TABLEA.ID = TABLEB.ID 
WHERE  TABLEA.ID = 1 
       AND TABLEB.DATE = (SELECT MAX(TABLEB.DATE) 
                          FROM   TABLEB 
                          WHERE  TABLEA.ID = 1) -- <-- Do you mean TABLEB.ID = 1 ??