两级分层如何分两层聚合，再行转列

炎同学要努力 2023-06-16 10:35:19

如何分两层聚合，再行转列
;with t1 as
(
select id = '00' ,班组id = 'A1',人员id = 'A',证书 = 'A'union all
select id = '00' ,班组id = 'A1',人员id = '班长',证书 = '班长证'union all
select id = '00' ,班组id = 'A1',人员id = 'C',证书 = 'A'union all
select id = '00' ,班组id = 'A2',人员id = 'A',证书 = 'A'union all
select id = '00' ,班组id = 'A3',人员id = '班长',证书 = '班长证书'union all
select id = '00' ,班组id = 'A4',人员id = '副班长',证书 = '证书'union all
select id = '00' ,班组id = 'A5',人员id = 'A',证书 = 'A'

)

目标：

id 班组数量班组名有班长班长有证书

00 5 A1 yes yes

00 5 A2

00 5 A3 yes yes

00 5 A4

00 5 A5

...全文

132 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

幸福感奇差 2023-06-16

打赏
举报

用一个窗口函数做统计即可

with t1 as
(
select id = '00' ,班组id = 'A1',人员id = 'A',证书 = 'A'union all
select id = '00' ,班组id = 'A1',人员id = '班长',证书 = '班长证'union all
select id = '00' ,班组id = 'A1',人员id = 'C',证书 = 'A'union all
select id = '00' ,班组id = 'A2',人员id = 'A',证书 = 'A'union all
select id = '00' ,班组id = 'A3',人员id = '班长',证书 = '班长证书'union all
select id = '00' ,班组id = 'A4',人员id = '副班长',证书 = '证书'union all
select id = '00' ,班组id = 'A5',人员id = 'A',证书 = 'A'

)
select id, sum(count(distinct 班组id)) over(partition by id) 班组数量, 班组id,
    case when sum(count(case when 人员id = '班长' then 1 else null end)) over(partition by id, 班组id) > 0 
            then 'yes'
    else null end 有班长,
    case when sum(count(case when 人员id = '班长' and 证书 like '班长证%' then 1 else null end)) over(partition by id, 班组id) > 0 
            then 'yes'
    else null end 班长有证书
from t1
group by id, 班组id

炎同学要努力 2023-06-16

@幸福感奇差你太厉害了，我使用分步做法，做不出来。

炎同学要努力 2023-06-16

@幸福感奇差只会，根据id分组得到班组数量然后再弄个表，根据id，班组id分组得到另一个表，但是连接起来又有问题了

炎同学要努力 2023-06-16

@幸福感奇差 with t1 as ( select id = '00' ,班组id = 'A1',人员id = 'A',证书 = 'A'union all select id = '00' ,班组id = 'A1',人员id = '班长',证书 = '班长证'union all select id = '00' ,班组id = 'A1',人员id = 'C',证书 = 'A'union all select id = '00' ,班组id = 'A2',人员id = 'A',证书 = 'A'union all select id = '00' ,班组id = 'A3',人员id = '班长',证书 = '班长证书'union all select id = '00' ,班组id = 'A4',人员id = '副班长',证书 = '证书'union all select id = '00' ,班组id = 'A5',人员id = 'A',证书 = 'A'union all select id = '01' ,班组id = 'A1',人员id = 'A',证书 = 'A'union all select id = '01' ,班组id = 'A1',人员id = '班长',证书 = '班长证'union all select id = '01' ,班组id = 'A1',人员id = 'C',证书 = 'A'union all select id = '01' ,班组id = 'A2',人员id = 'A',证书 = 'A'union all select id = '01' ,班组id = 'A3',人员id = '班长',证书 = '班长证书'union all select id = '01' ,班组id = 'A4',人员id = '副班长',证书 = '证书'union all select id = '01' ,班组id = 'A5',人员id = 'A',证书 = 'A' ) select id ,sum(count(distinct 班组id)) over(partition by id) 班组数量 -- 根据上一层的计算 ,班组id --,有班长 = (case when sum(count(case when 人员id = '班长' then 1 else null end)) over(partition by id, 班组id) > 0 then 'yes' else null end) --,班长有证书 = case when sum(count(case when 人员id = '班长' and 证书 like '班长证%' then 1 else null end)) over(partition by id, 班组id) > 0 then 'yes' else null end ,有班长2 = max(case when 人员id = '班长' then 'yes' else '' end) ,班长有证书2 = max(case when 人员id = '班长'and 证书 like '班长证%' then 'yes' else '' end) from t1 group by id, 班组id -- 你看看这是否有区别

炎同学要努力 2023-06-16