用分组结果从联接中删除重复项

Question

我使用带有EF Core的sql服务器。由于缺乏EF对组的支持，我运行原始sql查询。

所以，我有一张历史表

+------------+---------+------------------+
| HardwareId | StateId |    Timestamp     |
+------------+---------+------------------+
| Dev1       |       1 | 2019-10-11 11:00 |
| Dev2       |       2 | 2019-10-11 11:01 |
| Dev1       |       3 | 2019-10-11 11:09 |
| Dev2       |       1 | 2019-10-11 11:10 |
| Dev2       |       2 | 2019-10-11 11:10 |
| Dev1       |       3 | 2019-10-11 11:11 |
+------------+---------+------------------+

在这里，一些设备记录可以具有相同的时间戳。

现在我想知道每个设备的最后一个状态：

+------------+---------+------------------+
| HardwareId | StateId |    Timestamp     |
+-----==-----+---------+------------------+
| Dev2       |       1 | 2019-10-11 11:10 |
| Dev1       |       3 | 2019-10-11 11:11 |
+------------+---------+------------------+

我跑

SELECT H.TimeStamp, H.HardwareId, H.ErrorCode, SD.Description FROM History H
INNER JOIN 
(SELECT HardwareId, MAX(TimeStamp) LastDateTime from History 
GROUP BY HardwareId) AS LastStates 
ON H.TimeStamp = LastStates.LastDateTime
INNER JOIN StateDescription SD ON H.ErrorCode = SD.Id ORDER BY H.HardwareId

但是它给了我副本(由于一个给定设备的时间戳重复，一个设备的多个最后状态)。我宁愿选择这些州中的任何一个)

+------------+---------+------------------+
| HardwareId | StateId |    Timestamp     |
+------------+---------+------------------+
| Dev2       |       1 | 2019-10-11 11:10 |
| Dev2       |       2 | 2019-10-11 11:10 |
| Dev1       |       3 | 2019-10-11 11:11 |
+------------+---------+------------------+

添加不同的修复方法

SELECT DISTINCT H.TimeStamp, H.HardwareId, H.ErrorCode, SD.Description FROM History H
INNER JOIN 
(SELECT HardwareId, MAX(TimeStamp) LastDateTime from History 
GROUP BY HardwareId) AS LastStates 
ON H.TimeStamp = LastStates.LastDateTime
INNER JOIN StateDescription SD ON H.ErrorCode = SD.Id ORDER BY H.HardwareId

但是，如果我想在最终结果中添加一个记录Id (H.Id)，显然会再次得到重复的结果。

  SELECT DISTINCT H.TimeStamp, H.Id, H.HardwareId, H.ErrorCode, SD.Description FROM History H
    INNER JOIN 
    (SELECT HardwareId, MAX(TimeStamp) LastDateTime from History 
    GROUP BY HardwareId) AS LastStates 
    ON H.TimeStamp = LastStates.LastDateTime
    INNER JOIN StateDescription SD ON H.ErrorCode = SD.Id ORDER BY H.HardwareId

我如何才能为每个设备获得某种顶级的记录？

Answer 1

我只会用row_number()

select h.*
from (select h.*,
             row_number() over (partition by HardwareId order by timestamp desc) as seqnum
      from history h
     ) h
where seqnum = 1;

这将准确地选择每台设备每天一行。如果当天有重复，它将返回任意值。如果您想要所有这些，那么使用rank()而不是row_number()。