SAP HANA SQL问题：COUNT与CASE函数无法生成期望的用户分支聚合结果

场景：单个用户被分配至多个分支（一对多），需按用户分配的办公室进行分组，在SELECT语句中生成两列：

1. 用户被分配的办公室数量
2. 用户被分配的办公室名称

当前执行SQL得到的输出：

USER_LABEL          NUMBER_OFFICE_ASSIGNED   BRANCH_ASSIGNED      
-------------------------------------------------------------
FARAG                       1                  HQ
FARAG                       1                  SCM
FARAG                       1                  TCD
FARAG                       1                  TCM

期望得到的输出：

USER_LABEL          NUMBER_OFFICE_ASSIGNED   BRANCH_ASSIGNED      
-------------------------------------------------------------
FARAG                       4                  HQ,SCM,TCD,TCM

现有SQL代码：

SELECT us.USER_LABEL ,
count(od.office_id) AS "NUMBER_OFFICE_ASSIGNED",
CASE 
   WHEN od.OFFICE_ID=4 THEN 'HQ' 
   WHEN od.OFFICE_ID=5 THEN 'TCM'
   WHEN od.OFFICE_ID=6 THEN 'TCD'
   WHEN od.OFFICE_ID=7 THEN 'SCM'
   WHEN od.OFFICE_ID=8 THEN 'SSAAC' 
ELSE 'No branch assigned. Check with Admin'
END AS "BRANCH_ASSIGNED"    
FROM VIEW_USER_SETUP us
INNER JOIN USERS_DEPARTMENTS ud 
on(us.USER_ID=ud.USER_ID)
INNER JOIN DEPARTMENT_SETUP ds
on(ud.DEPARTMENT_ID=ds.DEPARTMENT_ID)
INNER JOIN DEPARTMENT_OFFICE do 
on(ds.DEPARTMENT_ID=do.DEPARTMENT_ID)
INNER JOIN OFFICE_DETAILS od 
on(do.OFFICE_ID=od.OFFICE_ID)
WHERE
   ds.ACTIVE_STATUS ='Y'
AND 
   do.ACTIVE_STATUS='Y'
AND 
   od.ACTIVE_STATUS='Y'
AND 
   us.ACTIVE_STATUS ='Y'
AND 
   us.USER_TYPE ='D'
AND 
   us.USER_LABEL NOT IN('Emergency Room','General Doctor','General Doctor Oph')
GROUP BY 
    us.USER_LABEL, 
    od.OFFICE_ID
ORDER BY 
    us.USER_LABEL ASC;

要实现将同一用户的多个分支合并为一行，需做以下核心调整：

1. 移除GROUP BY中的od.OFFICE_ID，仅保留用户维度分组
2. 用字符串聚合函数合并分支名称，同时调整计数逻辑避免重复

以下是基于Oracle数据库的修改后代码（其他数据库只需替换对应聚合函数）：

SELECT 
    us.USER_LABEL,
    COUNT(DISTINCT od.office_id) AS "NUMBER_OFFICE_ASSIGNED",
    LISTAGG(
        CASE 
            WHEN od.OFFICE_ID=4 THEN 'HQ' 
            WHEN od.OFFICE_ID=5 THEN 'TCM'
            WHEN od.OFFICE_ID=6 THEN 'TCD'
            WHEN od.OFFICE_ID=7 THEN 'SCM'
            WHEN od.OFFICE_ID=8 THEN 'SSAAC' 
            ELSE 'No branch assigned. Check with Admin'
        END, ','
    ) WITHIN GROUP (ORDER BY 
        CASE 
            WHEN od.OFFICE_ID=4 THEN 'HQ' 
            WHEN od.OFFICE_ID=5 THEN 'TCM'
            WHEN od.OFFICE_ID=6 THEN 'TCD'
            WHEN od.OFFICE_ID=7 THEN 'SCM'
            WHEN od.OFFICE_ID=8 THEN 'SSAAC' 
            ELSE 'No branch assigned. Check with Admin'
        END
    ) AS "BRANCH_ASSIGNED"
FROM VIEW_USER_SETUP us
INNER JOIN USERS_DEPARTMENTS ud ON us.USER_ID=ud.USER_ID
INNER JOIN DEPARTMENT_SETUP ds ON ud.DEPARTMENT_ID=ds.DEPARTMENT_ID
INNER JOIN DEPARTMENT_OFFICE do ON ds.DEPARTMENT_ID=do.DEPARTMENT_ID
INNER JOIN OFFICE_DETAILS od ON do.OFFICE_ID=od.OFFICE_ID
WHERE
    ds.ACTIVE_STATUS ='Y'
    AND do.ACTIVE_STATUS='Y'
    AND od.ACTIVE_STATUS='Y'
    AND us.ACTIVE_STATUS ='Y'
    AND us.USER_TYPE ='D'
    AND us.USER_LABEL NOT IN('Emergency Room','General Doctor','General Doctor Oph')
GROUP BY us.USER_LABEL
ORDER BY us.USER_LABEL ASC;

适配其他数据库的替换规则 #

• MySQL：将LISTAGG(...)替换为GROUP_CONCAT(CASE ... END SEPARATOR ',')
• SQL Server：将LISTAGG(...)替换为STRING_AGG(CASE ... END, ',')

关键修改说明 #

• GROUP BY仅保留用户维度：确保每个用户仅返回一行结果
• COUNT(DISTINCT od.office_id)：避免因关联关系导致的重复计数，保证统计的办公室数量准确
• 字符串聚合函数：将同一用户的所有分支名称合并为逗号分隔的字符串，ORDER BY子句用于控制合并后的分支排序

内容的提问来源于stack exchange，提问作者Luke