sql - 带有内部选择子句的按顺序分组的sql查询

Question

这是我的表关系。

Users, Roles(Uers 角色), Users_map(用户被分配到一个团队), Team(不同的团队), case_inventory

Users
TIN Name    Role_id Member_id
123 ABC     40      1   
321 CDE     30      0
678 FGH     40      2
987 XYZ     40      3
087 TYU     40      4

Users_map
tin team
123 1
321 2
678 2
987 2
087 5

Roles:
id role
30  Team President
40  Team Member

Team
id  Name 
1   Geo
2   Math
3   Science
4   Comp Si
5   Hist

team_inventory(caseId 是团队表中的 id)

teamId  TIN Assigned_to PREPARED
1       789 123         N
2       897 678         Y
3       987 123         Y
4       675 123         Y
2       900 321         Y
5       800 678         Y

我期望输出如下：这是基于 UI 的团队。如果选择是数学：

Role            Cases   Prepped %Completion
Team President  1       1       100%            <- 1 inventory is assigned to 321 who is a boardpresident
Team Member 2   2       2       100%            <- 2 inventory is assigned to 678 who is a board member whos member id is 2hence team member 2
Team Member 3   0       0       0%              <- math team has another team member (team member 3 - id 987 in users table) but no assignment hence 0 case 0 prepped
Summary         3       3       100%

我的查询看起来像这样，

select
        COALESCE(role,
        'Summary')role,
        SUM(CASE 
            WHEN PREPARED is null THEN 0 
            ELSE 1 
        END)  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 comp  
    from
        ( select
            CASE 
                WHEN r.id = 30 THEN r.name 
                ELSE r.name || ' ' || u.member_id 
            END AS role, 
            ti.prepared 
        from
            users u  
        left outer join
            roles r 
                on u.role_id = r.id   
        left outer join
            team_inventory ti   
                on   u.tin = ti.assigned_to  
        left outer join
            roles r 
                on u.role_id = r.id   
        left outer join
            user_map um 
                on  um.tin = u.tin  
        left outer join
            team t
                on ti.team_id = t.id   
        where
            r.id in(
                30,40
            ) 
            and ti.team_id =2 ) 
    group by
        rollup (role);

    

此查询工作正常，但 order by 不适用于 group by 。它在内部选择或外部选择中都没有影响。是否有一种有效的方法来编写此查询以获得所需的输出，其中还包括团队主席、团队成员和摘要的顺序？

标签： sqloracleoracle11g