时间戳

首页 > 解决方案 > SWQL/ SQL 分组并将一个字段中的多个结果组合成一个结果

sql - SWQL/ SQL 分组并将一个字段中的多个结果组合成一个结果

问题描述

有没有办法在下面的预期结果中实现这样的事情？

看到这个例子，但不确定应用，因为这里有很多“JOIN”。

当前原始声明：

SELECT TOP 1000 n.NodeID,t.Name as tagName ,le.LogEntryID,  le.DateTime, MessageDateTime
FROM Orion.OLM.LogEntry le
JOIN Orion.OLM.LogEntryTagAssignment leta on leta.LogEntryID=le.LogEntryID
JOIN Orion.OLM.Tags t on t.LogEntryTagID=leta.LogEntryTagID
JOIN Orion.Nodes n on n.NodeID = le.NodeID

GROUP by t.Name, n.NodeID, le.DateTime

结果：

NodeID  tagName         LogEntryID          DateTime                    MessageDateTime
210     ToBeDiscarded   1559852514889690000 2020-12-12T02:14:15.0530000 2020-12-12T10:14:15.0520000
210     Login Failure   1559852514889690000 2020-12-12T02:14:15.0530000 2020-12-12T10:14:15.0520000
210     MediaServer     1559852514889690000 2020-12-12T02:14:15.0530000 2020-12-12T10:14:15.0520000
210     RealTime        1559852514889690000 2020-12-12T02:14:15.0530000 2020-12-12T10:14:15.0520000
210     EscalateTo-L1   1560053462887030000 2020-12-12T05:33:52.4870000 2020-12-12T13:33:52.2710000
210     MediaServer     1560053462887030000 2020-12-12T05:33:52.4870000 2020-12-12T13:33:52.2710000
210     RealTime        1560053462887030000 2020-12-12T05:33:52.4870000 2020-12-12T13:33:52.2710000
210     EscalateTo-L2   1560053490082900000 2020-12-12T05:33:54.1070000 2020-12-12T13:33:54.1080000
210     MediaServer     1560053490082900000 2020-12-12T05:33:54.1070000 2020-12-12T13:33:54.1080000
210     RealTime        1560053490082900000 2020-12-12T05:33:54.1070000 2020-12-12T13:33:54.1080000

期待：

NodeID  tagName                                                 LogEntryID          DateTime                    MessageDateTime
210     ToBeDiscarded, Login Failure, MediaServer, RealTime     1559852514889690000 2020-12-12T02:14:15.0530000 2020-12-12T10:14:15.0520000
210     EscalateTo-L1, MediaServer, RealTime                    1560053462887030000 2020-12-12T05:33:52.4870000 2020-12-12T13:33:52.2710000
210     EscalateTo-L2, MediaServer, RealTime                    1560053490082900000 2020-12-12T05:33:54.1070000 2020-12-12T13:33:54.1080000

标签： sqlsolarwinds-orion

解决方案

Solarwinds 是一套（通常）构建在 SQL Server 之上的工具。SQL Server 现在提供string_agg()做你想做的事：

SELECT n.NodeID, le.DateTime, 
       STRING_AGG(t.Name, ', ') as tagNames,
       le.LogEntryID, MessageDateTime
FROM Orion.OLM.LogEntry le JOIN
     Orion.OLM.LogEntryTagAssignment leta
     ON leta.LogEntryID=le.LogEntryID JOIN
     Orion.OLM.Tags t 
     ON t.LogEntryTagID=leta.LogEntryTagID JOIN
     Orion.Nodes n on n.NodeID = le.NodeID
GROUP by t.Name, n.NodeID, le.DateTime, e.LogEntryID;

这在旧版本的 SQL Server 中是可能的，但语法更加复杂。

推荐阅读