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
