sql - 如何跨多列和多行 "Roll-Up"数据

Question

我有一个审计表，我们在其中记录对数据库中字段的更改。我有一个查询，我能够从审计中获取有关几列、它们记录的更改以及与适用 ID 关联的时间的数据子集。以下是输出示例:

ID      ada       IsHD  HDF   DTStamp
-----------------------------------------------------
68      NULL      0     0     2020-04-28 21:12:21.287
68      NULL      NULL  NULL  2020-04-17 14:59:49.700
68      No/Unsure NULL  NULL  2020-04-17 14:03:46.160
68      NULL      0     0     2020-04-17 13:49:49.720
102     NULL      NULL  NULL  2020-04-30 13:11:15.273
102     No/Unsure NULL  NULL  2020-04-20 16:00:35.410
102     NULL      1     1     2020-04-20 15:59:55.750
105     No/Unsure 1     1     2020-04-17 12:06:10.833
105     NULL      NULL  NULL  2020-04-13 07:51:30.180
126     NULL      NULL  NULL  2020-05-01 17:59:24.460
126     NULL      0     0     2020-04-28 21:12:21.287

我想弄清楚的是“汇总”给定 ID 的多行的最有效方法，以便保留最新的非空值，只为该 ID 留下一行。

也就是说，转这个:

68      NULL      0     0     2020-04-28 21:12:21.287
68      NULL      NULL  NULL  2020-04-17 14:59:49.700
68      No/Unsure NULL  NULL  2020-04-17 14:03:46.160
68      NULL      0     0     2020-04-17 13:49:49.720
102     NULL      NULL  NULL  2020-04-30 13:11:15.273
102     No/Unsure NULL  NULL  2020-04-20 16:00:35.410
102     NULL      1     1     2020-04-20 15:59:55.750

进入这个:

68      No/Unsure 0     0     2020-04-28 21:12:21.287
102     No/Unsure 1     1     2020-04-30 13:11:15.273

...等等。这几乎就像您要向下推结果的顶部并挤出所有 NULL，就像这样。

将上述结果转储到临时表中 @audit然后我运行以下查询:

SELECT DISTINCT a.[ID]
     , (SELECT TOP 1 [ADA]
        FROM @audit
        WHERE [ID] = a.[ID]
          AND [ADA] IS NOT NULL
        ORDER BY [DTStamp] DESC) AS 'ADA'
     , (SELECT TOP 1 [IsHD]
        FROM @audit
        WHERE [ID] = a.[ID]
          AND [IsHD] IS NOT NULL
        ORDER BY [DTStamp] DESC) AS 'IsHD'
     , (SELECT TOP 1 [HDF]
        FROM @audit
        WHERE [ID] = a.[ID]
          AND [HDF] IS NOT NULL
        ORDER BY [DTStamp] DESC) AS 'HDF'
     , (SELECT Max([DTStamp])
        FROM @audit
        WHERE [ID] = a.[ID]) AS 'DTStamp'
FROM @audit a
ORDER BY [ID]

这是我想出来的，它确实有效，但感觉非常笨拙且效率低下。有没有更好的方法来实现最终目标？

Answer 1

如果您想要每个 id 一行，则使用聚合:

select id, max(ada), max(IsHD), max(HDF), max(DTStamp)
from @audit a
group by id;

这适用于您提供的数据，并且似乎符合您想要的规则。