sql语句统计

sql更新语句

今天接到个需求需要具体如下：

1.数据库表中有一个字段有3中类型：分别为，团委，学工，个人上报荣誉。

2.展示内容：优先展示团委和学工两条，团委和学工不够使用个人上报补足两条。

根据需求可得知分3中情况，某用户有其中类型的3种都有则取团委和学工各一条。

如果只有一种类型则取2条。

如果有2种类型。则分为 团委学工，团委个人，学工个人三种情况。

具体语句实现如下：

/*更新只有一次荣誉的直接更新*/

select * from (
select xgh, type,cs,twory,count(xgh) OVER(partition by xgh) as rylxcs  from (
select xgh,type,count(*) as cs,
rtrim(split_part(string_agg(rymc,','),',',1)||','||split_part(string_agg(rymc,','),',',2),',') as twory
from ods.ODS_20HX_RYCH 
GROUP BY xgh,type )as tab1)as table1 where rylxcs = 1


/*更新只有3次荣誉的直接更新*/

select xgh,string_agg(case when type='团委' then mc 
when type ='学工' then mc end ,','
) as rymc from (
select xgh, type,cs,mc,count(xgh) OVER(partition by xgh) as rylxcs  from (
select xgh,type,count(*) as cs,min(rymc)as mc from ods.ODS_20HX_RYCH 
GROUP BY xgh,type )as t1) as tab1  where rylxcs = 3
GROUP BY xgh 

/*更新2种荣誉的 要处理多次  区分多种情况*/

select xgh,split_part(xgmc,',',1)||','||split_part(twmc,',',1)as rymc from (
select 
xgh,sum(case when type='学工' then cs end )as xgcs,
max(case when type='学工' then twory end )as xgmc,
sum(case when type='个人上报' then cs end) as grcs,
max(case when type='个人上报' then twory end) as grmc,
sum(case when type='团委' then cs end) as twcs,
max(case when type='团委' then twory end) as twmc
from (
select xgh, type,cs,twory,count(xgh) OVER(partition by xgh) as rylxcs  from (
select xgh,type,count(*) as cs,
rtrim(split_part(string_agg(rymc,','),',',1)||','||split_part(string_agg(rymc,','),',',2),',') as twory
from ods.ODS_20HX_RYCH 
GROUP BY xgh,type )as tab1)as table1  where rylxcs = 2
GROUP BY xgh)as table2
where xgcs is not null and twcs is not null 
union all 
select xgh,twmc from (
select 
xgh,sum(case when type='学工' then cs end )as xgcs,
max(case when type='学工' then twory end )as xgmc,
sum(case when type='个人上报' then cs end) as grcs,
max(case when type='个人上报' then twory end) as grmc,
sum(case when type='团委' then cs end) as twcs,
max(case when type='团委' then twory end) as twmc
from (
select xgh, type,cs,twory,count(xgh) OVER(partition by xgh) as rylxcs  from (
select xgh,type,count(*) as cs,
rtrim(split_part(string_agg(rymc,','),',',1)||','||split_part(string_agg(rymc,','),',',2),',') as twory
from ods.ODS_20HX_RYCH 
GROUP BY xgh,type )as tab1)as table1  where rylxcs = 2
GROUP BY xgh)as table2
where grcs is not null and twcs > 1
union all 
select xgh,xgmc from (
select 
xgh,sum(case when type='学工' then cs end )as xgcs,
max(case when type='学工' then twory end )as xgmc,
sum(case when type='个人上报' then cs end) as grcs,
max(case when type='个人上报' then twory end) as grmc,
sum(case when type='团委' then cs end) as twcs,
max(case when type='团委' then twory end) as twmc
from (
select xgh, type,cs,twory,count(xgh) OVER(partition by xgh) as rylxcs  from (
select xgh,type,count(*) as cs,
rtrim(split_part(string_agg(rymc,','),',',1)||','||split_part(string_agg(rymc,','),',',2),',') as twory
from ods.ODS_20HX_RYCH 
GROUP BY xgh,type )as tab1)as table1  where rylxcs = 2
GROUP BY xgh)as table2
where grcs is not null and xgcs > 1
union all 
select xgh,split_part(grmc,',',1)||','||twmc as rymc from (
select 
xgh,sum(case when type='学工' then cs end )as xgcs,
max(case when type='学工' then twory end )as xgmc,
sum(case when type='个人上报' then cs end) as grcs,
max(case when type='个人上报' then twory end) as grmc,
sum(case when type='团委' then cs end) as twcs,
max(case when type='团委' then twory end) as twmc
from (
select xgh, type,cs,twory,count(xgh) OVER(partition by xgh) as rylxcs  from (
select xgh,type,count(*) as cs,
rtrim(split_part(string_agg(rymc,','),',',1)||','||split_part(string_agg(rymc,','),',',2),',') as twory
from ods.ODS_20HX_RYCH 
GROUP BY xgh,type )as tab1)as table1  where rylxcs = 2
GROUP BY xgh)as table2
where grcs is not null and twcs = 1
union all 
select xgh,split_part(grmc,',',1)||','||xgmc as rymc from (
select 
xgh,sum(case when type='学工' then cs end )as xgcs,
max(case when type='学工' then twory end )as xgmc,
sum(case when type='个人上报' then cs end) as grcs,
max(case when type='个人上报' then twory end) as grmc,
sum(case when type='团委' then cs end) as twcs,
max(case when type='团委' then twory end) as twmc
from (
select xgh, type,cs,twory,count(xgh) OVER(partition by xgh) as rylxcs  from (
select xgh,type,count(*) as cs,
rtrim(split_part(string_agg(rymc,','),',',1)||','||split_part(string_agg(rymc,','),',',2),',') as twory
from ods.ODS_20HX_RYCH 
GROUP BY xgh,type )as tab1)as table1  where rylxcs = 2
GROUP BY xgh)as table2
where grcs is not null and xgcs = 1