我的查詢為用戶分配到的每個組回傳多行，示例如下：
我需要為每個用戶將每個組連接在一行上，例如：groupA、groupB、groupC
我嘗試使用 SUBSTRING、SUB SELECT、GROUP_CONCAT.. 到目前為止沒有任何效果。
這是回傳示例影像的查詢
SELECT DISTINCT { customer.uid } AS email,
{ customer.lastLogin } AS lastLogin,
{ customer.company } AS company,
{ unit.topLevel } AS site,
{group.uid} AS groups
FROM { B2BCustomer AS customer
JOIN CustomerCMSSiteRelation AS site
ON { site.source } = { customer.PK }
JOIN B2BUnit AS unit
ON {customer.defaultB2BUnit} = {unit.pk}
JOIN PrincipalGroupRelation AS rel ON {customer:PK} = {rel:source}
JOIN UserGroup AS group ON {rel:target} = {group:PK} }
WHERE {unit.topLevel} = 'AR'
uj5u.com熱心網友回復：
- 如果
string_agg()不起作用，請將其替換為group_concat
with main as (
SELECT DISTINCT { customer.uid } AS email,
{ customer.lastLogin } AS lastLogin,
{ customer.company } AS company,
{ unit.topLevel } AS site,
{group.uid} AS groups
FROM { B2BCustomer AS customer
JOIN CustomerCMSSiteRelation AS site
ON { site.source } = { customer.PK }
JOIN B2BUnit AS unit
ON {customer.defaultB2BUnit} = {unit.pk}
JOIN PrincipalGroupRelation AS rel ON {customer:PK} = {rel:source}
JOIN UserGroup AS group ON {rel:target} = {group:PK} }
WHERE {unit.topLevel} = 'AR'
)
select
email,
lastLogin,
company,
site,
STRING_AGG(groups,',') as groups_combined
from main
group by 1,2,3,4
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/529785.html