T-SQL STRING_AGG问题不知道是写得不好还是干不通

Question

为什么我在第一部分中没有看到我的 i.inventoryId 的分隔？ 但是当我在其他选择中选择它们时，我得到了更多的结果。 还有"绝地武士" == (玩家头像ID 6(

https://i.ibb.co/XW4NJC7/image.png

SELECT p.FirstName [Spelers Voornaam]
,p.LastName [Spelers Achternaam]
,pa.AvatarName [Spelers Avatarnaam]
,pa.FamilyName [Familie's Groeps Naam]
,Av.Type [Avatar's Type]
,string_agg (i.InventoryId, ',') as [In Inventory]
FROM Player AS p
LEFT JOIN PlayerAvatar AS pa ON p.PlayerId = pa.PlayerId
LEFT JOIN Avatar AS Av ON pa.AvatarId = Av.AvatarId
LEFT JOIN Avatar AS a ON pa.AvatarId = a.AvatarId
LEFT JOIN Inventory as i on  i.InventoryId = pa.InventoryId
LEFT JOIN Item as it on it.ItemId = i.ItemId
WHERE pa.AvatarName = 'Jedi'
GROUP BY p.FirstName, p.LastName, pa.AvatarName, pa.FamilyName, av.Type, i.InventoryId
SELECT *
from Inventory
Where PlayerAvatarId = 6

Answer 1

我认为您不想按 InventoryId 进行分组，如果这是您要连接的内容......试试这个：

编辑时，您需要删除与行与行不同的列。

SELECT p.FirstName [Spelers Voornaam]
,p.LastName [Spelers Achternaam]
,pa.FamilyName [Familie's Groeps Naam]
,string_agg (i.InventoryId, ',') as [In Inventory]
FROM Player AS p
LEFT JOIN PlayerAvatar AS pa ON p.PlayerId = pa.PlayerId
LEFT JOIN Avatar AS Av ON pa.AvatarId = Av.AvatarId
LEFT JOIN Avatar AS a ON pa.AvatarId = a.AvatarId
LEFT JOIN Inventory as i on  i.InventoryId = pa.InventoryId
LEFT JOIN Item as it on it.ItemId = i.ItemId
GROUP BY p.FirstName, p.LastName, pa.AvatarName, pa.FamilyName, av.Type

或者，您也可以聚合这些列。

SELECT p.FirstName [Spelers Voornaam]
,p.LastName [Spelers Achternaam]
,string_agg(pa.AvatarName,',') [Spelers Avatarnaam]
,pa.FamilyName [Familie's Groeps Naam]
,string_agg(Av.Type,',') [Avatar's Type]
,string_agg (i.InventoryId, ',') as [In Inventory]
FROM Player AS p
LEFT JOIN PlayerAvatar AS pa ON p.PlayerId = pa.PlayerId
LEFT JOIN Avatar AS Av ON pa.AvatarId = Av.AvatarId
LEFT JOIN Avatar AS a ON pa.AvatarId = a.AvatarId
LEFT JOIN Inventory as i on  i.InventoryId = pa.InventoryId
LEFT JOIN Item as it on it.ItemId = i.ItemId
GROUP BY p.FirstName, p.LastName, pa.FamilyName,