获取数据表中各NIC对应的最新状态数据

样本数据 #

需求 #

获取表中各NIC对应的最新状态，以ADT时间字段判定更新先后。

期望结果 #

已尝试的SQL语句 #

SELECT
  u1.NIC,
  u1.Status,
  u1.Remarks,
  u1.ADT
FROM callsc2 u1
  LEFT OUTER JOIN callsc2 u2
    ON u1.NIC = u2.NIC
    AND u1.ADT > u2.ADT

解决方案 #

方法1：使用窗口函数（推荐，适配多数现代数据库） #

通过ROW_NUMBER()按NIC分组，按ADT降序排序后，取每组第一条数据：

SELECT NIC, Status, ADT
FROM (
    SELECT 
        NIC, 
        Status, 
        ADT,
        ROW_NUMBER() OVER (PARTITION BY NIC ORDER BY ADT DESC) AS rn
    FROM callsc2
) t
WHERE rn = 1;

方法2：修正原有左连接写法 #

原有查询缺少过滤条件，添加WHERE u2.ADT IS NULL即可筛选出每组中没有更晚记录的最新数据：

SELECT
  u1.NIC,
  u1.Status,
  u1.ADT
FROM callsc2 u1
LEFT OUTER JOIN callsc2 u2
  ON u1.NIC = u2.NIC
  AND u1.ADT < u2.ADT
WHERE u2.ADT IS NULL;

方法3：分组子查询关联获取结果 #

先按NIC分组得到每个组的最新ADT，再关联原表匹配对应状态：

SELECT c.NIC, c.Status, c.ADT
FROM callsc2 c
INNER JOIN (
    SELECT NIC, MAX(ADT) AS max_adt
    FROM callsc2
    GROUP BY NIC
) t ON c.NIC = t.NIC AND c.ADT = t.max_adt;

内容的提问来源于stack exchange，提问作者RITZ