按ID获取多个列的最大值(MSSQL)

Question

我在网上看到了很多类似的帖子，但是没有一个是解决我的问题。

这是我的样本数据：

ID      CID     NARID   NATID       NADate      EID     AEDate
1       1655    1       4           12/1/12     202     6/4/14 11:37:01
2       1655    1       7           12/1/12     202     6/4/14 11:37:12
5       1655    2       65          1/13/14     587     6/4/14 11:37:00
29      3165    1       6           4/15/14     7       6/4/14 11:37:00
300     3165    1       6           6/30/14     7       6/4/14 11:33:50
295     3165    2       64          6/11/14     7       6/4/14 11:37:00
302     3165    2       63          7/24/14     7       6/4/14 11:41:24
303     3165    2       67          7/24/14     7       6/4/14 15:59:06

我首先要获取每个CID和NARID的最大NADate：

ID      CID     NARID   NATID       NADate      EID     AEDate
1       1655    1       4           12/1/12     202     6/4/14 11:37:01
2       1655    1       7           12/1/12     202     6/4/14 11:37:12
5       1655    2       65          1/13/14     587     6/4/14 11:37:00
300     3165    1       6           6/30/14     7       6/4/14 11:33:50
302     3165    2       63          7/24/14     7       6/4/14 11:41:24
303     3165    2       67          7/24/14     7       6/4/14 15:59:06

然后从这些结果中获取具有最大 AEDate 的记录（以及所有其他对应字段）：

ID      CID     NARID   NATID       NADate      EID     AEDate
2       1655    1       7           12/1/12     202     6/4/14 11:37:12
5       1655    2       65          1/13/14     587     6/4/14 11:37:00
300     3165    1       6           6/30/14     7       6/4/14 11:33:50
303     3165    2       67          7/24/14     7       6/4/14 15:59:06

数据库类型为MSSQL 2005。

- versionke

---

+1 是指在一个相对常见的问题上，实际上有一个不太常见的变体。 - Gordon Linoff

Answer 1

我认为最简单的方法是使用 dense_rank():

select t.*
from (select t.*,
             dense_rank() over (partition by cid
                                order by nadate desc, cast(edate as date) desc
                               ) as seqnum
      from table t
     ) t
where seqnum = 1;

您需要将edate转换为日期格式，使用cast(edate to date)可以让查询只考虑edate的日期部分。您需要使用dense_rank()函数，这样可以返回最近日期的所有行。

- Gordon Linoff

---

你正在以 cid 进行分组，但问题要求按 cid, narid 进行分组。此外，如果你将其转换为 date，则会丢弃时间部分以进行排序。 - Andomar

根据您的解决方案进行了一些修改后，这个问题得到了解决：SELECT t.* FROM ( SELECT t.*, dense_rank() OVER ( PARTITION BY cid, narid ORDER BY nadate DESC, aedate DESC ) AS seqnum FROM TABLE t ) t WHERE seqnum = 1; - versionke

Answer 2

您可以使用row_number()在每个(cid, narid)组内分配编号。如果按nadate desc，aedate desc的顺序分配行号，则行号为1的行将是您要查找的行：

select  *
from    (
        select  row_number() over (
                    partiton by cid, narid
                    order by nadate desc, aedate desc) as rn
        ,       *
        from    YourTable
        ) as SubQueryAlias
where   rn = 1

- Andomar

---

如果您的数据中存在两个相同记录的可能性（除了ID之外），则似乎此解决方案将排除重复行，而使用dense_rank()的上述解决方案则不会。虽然这种情况在我的数据中永远不会发生，但它也是一个可行的解决方案，适用于其他出现重复行的情况。在row_number()之前添加*是必要的，以显示所有列。 - versionke

Answer 3

    WITH TEMP AS
        (    
        SELECT CID,NARID,MAX(NADATE)  AS TEMPDATE
        FROM TABLE
        GROUP BY CID,NARID
        )
    SELECT A.ID,A.CID,A.NARID,A.NATID,A.NADate,A.EID,MAX(A.AEDate)
      FROM TABLE A INNER JOIN TEMP 
    ON A.CID=TEMP.CID AND A.NARID=TEMP.NARID AND A.NADATE=TEMP.TEMPDATE
    GROUP BY A.ID,A.CID,A.NARID,A.NATID,A.NADate,A.EID;

- VJ Hil

---

这将获取顶部的nadate，但不会使用aedate解决平局。 - Andomar