带有内部选择子句的sql order by和按汇总分组

Question

我的查询如下图所示。输出

电流输出

Role            Cases prepped % Completed    
State Member 1  10    5       50%
State Member 2  10    7       70%
State President 10    2       20%
Summary         30    14      46.6%

预期产量

Role            Cases prepped % Completed
State President 10    2       20%
State Member 1  10    5       50%
State Member 2  10    7       70%
Summary         30    14      46%

角色表

id name
30 State President
40 State Member

这是我的查询

SELECT COALESCE(ROLE, 'Summary') ROLE,   
       count(*) AS cases, 
       SUM(CASE WHEN PREPARED = 'Y' THEN 1
                ELSE 0
           END) AS prepped, 
 round(avg(case when prepared = 'Y' then 100 else 0 end),2)||'%' 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.id = ci.case_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)
GROUP BY ROLLUP (ROLE);

我现在要根据角色对行进行排序。第一个记录应该是州总统，然后是州议员1.州议员2.依此类推。我试图在内部子句中按顺序排序，但这没有帮助。它没有任何作用。添加外部选择也不会更改任何内容。任何帮助表示高度赞赏。谢谢你。

Answer 1

你可以做这样的事情。我没有您的输入数据，所以我SCOTT.EMP改用了。

注意几件事。我按分组JOB，并GROUPING(JOB)在中使用SELECT（TOTAL为摘要行添加标签）和中ORDERBY。由于我JOB在SELECT（输出列）中重用了列名，因此在ORDER BYI中，我必须小心限定列名JOB（为了清楚起见，我指的是输入表列，而不是指-中的列，SELECT如果中的列名ORDERBY不合格）。然后，需要对中的列名进行限定ORDERBY，这迫使我在该FROM子句中对该表进行别名化（否则，我将不得不在所有地方都携带完整的表名）。

如果可以的话，GROUPING在中SELECT（而不是NVL）中使用函数特别重要。您不希望为工作组添加标签-
您只希望为汇总行添加标签。这一点甚至使很多非常高级的程序员感到困惑。JOB``null``null``TOTAL

我展示了如何“手动”确定顺序：PRESIDENT首先是，然后是MANAGER，然后是所有其他作业（按字母顺序排列）。如果您将优先级顺序保存在某个地方（例如，在表中），则可以加入该表并使用排序列而不是CASE查询中的“手动”表达式。

select case grouping(job) when 0 then job else 'TOTAL' end as job
     , sum(sal) as total_salary
from   scott.emp e
group  by rollup(job)
order  by grouping(e.job)       -- to get the total in the last row
        , case e.job when 'PRESIDENT' then 1 when 'MANAGER' then 2 end
        , e.job
;

JOB       TOTAL_SALARY
--------- ------------
PRESIDENT         5000
MANAGER           8275
ANALYST           6000
CLERK             4150
SALESMAN          5600
TOTAL            29025