SQL GROUP BY ROLLUP结果排序问题：如何按指定角色顺序输出

我明白你遇到的困扰：用ROLLUP生成汇总行后，常规的ORDER BY没办法按照你想要的角色顺序（State President → State Member 1/2 → Summary）输出结果。这是因为字符串默认排序会把"State Member"系列放在"State President"前面，而且ROLLUP生成的Summary行也需要特殊处理位置。

下面是修改后的SQL查询，完美匹配你的期望输出：

SELECT 
  COALESCE(ROLE, 'Summary') AS ROLE, 
  count(*) AS cases, 
  SUM(CASE WHEN PREPARED = 'Y' THEN 1 ELSE 0 END) AS prepped, 
  -- 调整round参数为0，输出整数百分比
  round(avg(case when prepared = 'Y' then 100 else 0 end), 0)||'%' AS "% Completed" 
FROM (
  SELECT 
    CASE WHEN r.id = 30 THEN r.name ELSE r.name || ' ' || u.case_member_id END AS ROLE, 
    bi.prepared 
  FROM cases c 
  LEFT JOIN case_inventory ci ON ci.case_id = c.id AND c.delete_date IS NULL AND ci.case_id = 40 
  LEFT JOIN users u ON ci.assigned_to = u.id 
  LEFT JOIN ROLES r ON u.role_id = r.id 
  LEFT JOIN user_cases_map uc ON c.id = uc.case_id AND uc.id = 1572919346
) AS subquery
GROUP BY ROLLUP (ROLE)
ORDER BY
  -- 给不同角色分配排序优先级
  CASE COALESCE(ROLE, 'Summary')
    WHEN 'State President' THEN 1
    WHEN 'Summary' THEN 3
    ELSE 2
  END,
  -- 对State Member按后缀数字升序排序
  CASE WHEN COALESCE(ROLE, 'Summary') LIKE 'State Member %' 
       THEN CAST(SUBSTRING(COALESCE(ROLE, 'Summary') FROM 'State Member (\d+)') AS INT) 
       ELSE 0 
  END;

关键修改点说明： #

1. 自定义排序规则：
   在ORDER BY中用CASE语句给每个角色设定排序权重：

   • State President权重为1，确保排在最前面
   • Summary权重为3，固定在最后一行
   • 所有State Member系列权重为2，排在中间

2. State Member的数字排序：
   通过SUBSTRING提取State Member后面的数字，转为整数后排序，保证"State Member 1"在"State Member 2"前面，避免出现字符串排序导致的"10"排在"2"前面的问题。

3. 百分比格式调整：
   把round函数的第二个参数从2改为0，让百分比显示为整数（比如46%而非46.6%），完全匹配你的期望输出。

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