时间戳

首页 > 解决方案 > 过滤掉，以便每个唯一 ID 仅显示最新日期

sql - 过滤掉，以便每个唯一 ID 仅显示最新日期

问题描述

有多个设备 ID 具有相同的 ID，但 Condition_Dates 不同，我只想显示每个唯一设备 ID 的最新状态日期。

Select equipment.ID as EquipmentID,
       equipment.DESCRIPTION, 
       CONDITION_DATE, 
       CONDITION_TYPE.DESCRIPTION
from CONDITION
  LEFT JOIN EQUIPMENT ON condition.GUID_EQUIPMENT = EQUIPMENT.GUID
  LEFT JOIN Condition_type ON Condition.GUID_CONDITION_TYPE = condition_type.guid
WHERE IS_DEACTIVATED = '0' 
  AND equipment.HAS_CONDITION_CONTROL = '1' 
  AND Condition_type.DESCRIPTION is not null

Equipment ID | Condition_Date
3345.02      | 2013-08-29
3345.02      | 2009-05-20 
3346         | 2019-07-05
3345.02      | 2001-01-01
3346         | 2008-08-02

我希望它只显示

Equipment ID | Condition_Date
3345.02      | 2013-08-29
3346         | 2019-07-05

标签： sqlsql-servergreatest-n-per-group

解决方案

以下如何，在日期字段和 GROUP BY Id 上使用 MAX 进行聚合：

SELECT equipment.ID AS EquipmentID,
       MAX(CONDITION_DATE), 
FROM CONDITION
  LEFT JOIN EQUIPMENT 
         ON condition.GUID_EQUIPMENT = EQUIPMENT.GUID
  LEFT JOIN Condition_type 
         ON Condition.GUID_CONDITION_TYPE = condition_type.guid
WHERE IS_DEACTIVATED = '0' 
  AND equipment.HAS_CONDITION_CONTROL = '1' 
  AND Condition_type.DESCRIPTION IS NOT NULL
GROUP BY equipment.ID

推荐阅读