GROUP BY SQL语句中的Sub SELECT

I have a table where I parsed a whole bunch of VPN logs. The table does not have a primary key field and the guid field is how everything is correlated. I want to select rows grouped by the guid. and run a sub select within the group for a specific string. Not sure if this makes sense, here's the query:

我有一个表,我解析了一大堆VPN日志。该表没有主键字段,guid字段是所有内容的关联方式。我想选择由guid分组的行。并在组中为特定字符串运行子选择。不确定这是否有意义,这是查询:

SELECT [guid]
  ,Max([username]) AS [User]
  ,MIN([datetime]) AS Minimum
  ,Max([datetime]) AS Maximum
  ,DATEDIFF(mi, MIN([datetime]), Max([datetime])) AS "Duration in Minutes"
  ,(SELECT top 1 [data] FROM [Admin].[dbo].[VPNLogs] WHERE [data] LIKE '%The user was active for%') AS "Data"
FROM [Admin].[dbo].[temp_VPNLogs]
GROUP BY [guid]
ORDER BY "Duration in Minutes" DESC

This works well with the exception that the sub select for the data field just returns the same row for all results when I want it to select this based on the Group By. Every VPN connection entry has 4 lines with the same guid this is why I group by the guid, and I want to specifically select the data field that has the string "The user was active for" for each group. Let me know if further clarification is needed.

这种情况很好,但是当我希望它根据Group By选择它时,数据字段的子选择只返回所有结果的同一行。每个VPN连接条目有4行具有相同的guid这就是我按guid分组的原因,我想要专门为每个组选择具有字符串“用户有效”的数据字段。如果需要进一步澄清,请与我们联系。

Thanks!

1 个解决方案

#1

co related sub query is what you need here:

co相关的子查询是你需要的:

SELECT [guid]
  ,Max([username]) AS [User]
  ,MIN([datetime]) AS Minimum
  ,Max([datetime]) AS Maximum
  ,DATEDIFF(mi, MIN([datetime]), Max([datetime])) AS "Duration in Minutes"
  ,(SELECT top 1 [data] FROM [Admin].[dbo].[VPNLogs] V1 WHERE [data] LIKE '%The user was active for%' AND v1.guid = v.guid) AS "Data"
FROM [Admin].[dbo].[temp_VPNLogs] V
GROUP BY [guid]
ORDER BY "Duration in Minutes" DESC