代码之家 › 专栏 › 技术社区 › stackoverflowuser

t-sql查询:检索最新的行方法

sql-server-2008 tsql sql-server sql

stackoverflowuser · 技术社区 · 15 年前

根据下表

Table_A

ID   Rev  Description
-----------------------------------
1    1    Some text.
1    2    Some text. Adding more.
1    3    Some text. Ading more & more.

为此,我将执行以下操作:

;with AllDescriptions As
(
 select 
        ID
        , Rev
        , Description 
        , ROW_NUMBER() over (partition by ID order by Rev desc) as RowNum
        from Table_A
        Where ID = 1
)
select ID, Rev, Description from AllDescription
where RowNum = 1

select b.* from 
(
 select ID, MAX(Rev) as MaxRev 
 from Table_A 
 where ID = 1
 group by ID
) as a
inner join 
(
 select ID, Rev, Description from Table_A where ID = 1
) as b
on a.ID = b.ID and a.MaxRev = b.Rev

3 回复 | 直到 15 年前

Community Mohan Dere 8 年前

第二种方法看起来像以前的SQLServer2000方法 Row_Number() Greatest-n-per-group problem

要评估它们,您应该通过运行 SET STATISTICS IO ON

当然,对于您给出的具体示例,下面的方法同样适用

 select TOP 1
        ID
        , Rev
        , Description 
        from Table_A
        Where ID = 1
ORDER BY Rev desc

momo 15 年前

如果父表中每个ID只列出一次,则这有时会优于其他策略,包括行数解决方案:

SELECT
   X.*
FROM
   ParentTable P
   CROSS APPLY (
      SELECT TOP 1 *
      FROM Table_A A
      WHERE P.ID = A.ID
      ORDER BY A.Rev DESC
   ) X

还有疯狂的、专利的(开玩笑的)单扫描魔术查询,它的性能通常也优于其他方法:

SELECT
   ID,
   Rev = Convert(int, Substring(Packed, 1, 4)),
   Description = Convert(varchar(100), Substring(Packed, 5, 100))
FROM
   (
      SELECT
         ID,
         Packed = Max(Convert(binary(4), Rev) + Convert(varbinary(100), Description))
      FROM Table_A
      GROUP BY ID
   ) X

注意:不建议使用最后一种方法,但是在MS-Access中模拟第一个/最后一个聚合是很有趣的。