SQL Server Group by与多个表得到3行，当只需要1

Question

我使用以下SQL代码，其中Id和DateTime列是相同的，我想将它们分组在一起。Ref是不同的，在这个例子中，我有三个名字与相同的Id和DateTime。我想得到一行这样的数据输出:

Id       DateTime                 Ref
---------------------------------------------
3        Sep 4 2021 08:00:00    Billy Joel
Chris Farley
Joe Blow   

相反，我得到3行数据:

Id       DateTime                Ref
---------------------------------------------
3        Sep 4 2021 08:00:00    Billy Joel
3        Sep 4 2021 08:00:00    Chris Farley
3        Sep 4 2021 08:00:00    Joe Blow   

下面是我的SQL代码和sqltest站点与我的数据和SQL查询。有人能帮忙吗?

Select 
a.Id, a.DateTime, 
(o.cFName + ' ' + o.cLName) as Ref 
From 
GameAssignment g 
Left Outer Join 
games a on g.gameId = a.Id 
Left Outer Join 
FieldGym f On a.FieldGym = f.Id 
Left Outer Join 
Official o On o.Id = g.OfficialId
Where 
Convert(date, a.DateTime) = '09/04/2021' 
And f.Id = 3
Group by 
a.Id, o.cFName, o.cLName, a.DateTime 
Order by 
a.DateTime

SQL测试站点https://sqltest.net/#1422359

Answer 1

与SQL Server似乎你最接近的选项是STRING_AGG()函数。这对你来说够好了吗?

Answer 2

修改你的代码使用一个for xml路径解决方案:

Select 
a.Id, a.DateTime, 
STUFF((select '|' + o.cFName + ' ' + o.cLName
from Official o 
where o.Id = g.OfficialId
order by o.cFName,o.cLName
for xml path(''))
,1,1,'')
as Ref 
From 
GameAssignment g 
Left Outer Join 
games a on g.gameId = a.Id 
Left Outer Join 
FieldGym f On a.FieldGym = f.Id 

Where 
Convert(date, a.DateTime) = '09/04/2021' 
And f.Id = 3
Group by 
a.Id, a.DateTime 
Order by 
a.DateTime

这将在列中为您提供一个管道分隔的列表，您可以选择其他内容或在表示层中使用vbCRLF或environment.NewLine.

试一试。

Answer 3

由于名称将显示在单独的行中，因此使用lag()来检索前一个id。如果id和prev_id相同，则id和日期将为空字符串。基本上是在报告工具中处理的。大多数情况下，名称将以逗号分隔。如果Null同时需要id和datetime，则使用Null而不是空字符串。

-- SQL Server
SELECT CASE WHEN t.id = t.prev_val THEN '' ELSE CAST(t.id AS varchar(10)) END id
, CASE WHEN t.id = t.prev_val THEN '' ELSE CONVERT(VARCHAR, t.DateTime, 109) END DateTime
, t.Ref
FROM (Select 
a.Id, a.DateTime, 
(o.cFName + ' ' + o.cLName) as Ref,
LAG(a.Id) OVER (Order by a.DateTime) prev_val
From 
GameAssignment g 
Left Outer Join 
games a on g.gameId = a.Id 
Left Outer Join 
FieldGym f On a.FieldGym = f.Id 
Left Outer Join 
Official o On o.Id = g.OfficialId
Where 
Convert(date, a.DateTime) = '09/04/2021' 
And f.Id = 3
Group by 
a.Id, o.cFName, o.cLName, a.DateTime) t 
Order by t.DateTime

请从url https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=8530d66b175704a2ef6259a1021d46aa查看