「竞价网赚」 sql语句统计-冷门项目

本文最后更新于2020年8月20日,已超过30天没有更新,如果文章内容失效,请反馈给我们,谢谢!

摘要

sql更新语句 今天接到个需求需要具体如下: 1.数据库表中有一个字段有3中类型:分别为学工系统字段不能为null,团委,学工学工系统字段不能为null,个人上报荣誉。 ...

 

本站长期更新如何网赚以及网赚技巧相关教程

同时本站也是网赚论坛大全,会给大家带来网赚钱最多是什么网相关项目

本站内容好好研究,轻轻松松做出月入过万的副业


正文:

 

sql更新语句

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

1.数据库表中有一个字段有3中类型:分别为学工系统字段不能为null,团委,学工学工系统字段不能为null,个人上报荣誉。

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

转载请注明:太白网赚平台 » 「竞价网赚」 sql语句统计

当人们无法选择自己的未来时,就会珍惜自己选择过去的权利。回忆的动人之处就在于可以重新选择,可以将那些毫无关联的往事重新组合起来,从而获得了全新的过去。 

网赚58:专注互联网冷门项目,紧跟最新最热创业信息,分享经验心得,揭秘套路,是网赚客的聚集地,主要项目有高回报项目和薅羊毛活动

网赚58-专注互联网冷门项目
  • 风险提示:本站项目资讯均收集自互联网,投资有风险,各位需谨慎,如有损失,概不负责
  • 一手消息:请加入QQ禁言群,获取最新消息

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: