PostgreSQL数据库高级sql总结2

1、postgresql 数据库update 去重sql

先按重复记录fk分组找最小的记录，然后更新id 不在子查询中的数据

update td_f_stop_history set del_flag='1' where "id" not in

(select "min"("id") from td_f_stop_history where allowed_recover='1' and del_flag='0'

group by fk HAVING "count"(fk)>1)

and allowed_recover='1' and del_flag='0';

2、从一个表查询数据添加到另外一个表中

insert into tf_admin_org2

(org_name,adm_id,org_parent_id,org_code,sort,level,adm_name,del_flag,org_id,unit_id,unit_name,unit_org_case,unit_org_id,is_creat_org,type,change_flag,adm_parent_id,adm_code)

select

d01001,id,parent_id,org_code,sort,level,name,del_flag,org_id,bid,b01001,b99011,d01014,b90007,b90006,change_flag,adm_parent_id,adm_code from tf_admin_org

3、从一张表更新同步到另外一张表

postgresql数据库，软弱涣散基层党组织将一个表中的数据同步到另外一个表

UPDATE tb_village_city_work_progress set org_code=b.org_code

from tf_admin_org b

where tb_village_city_work_progress.adm_id=b.adm_id and tb_village_city_work_progress.adm_id='e6a0755f-7053-45ab-be62-3c0cf6dc6012'

4、查询有多个书记的村庄

SELECT

*

FROM

tb_village_detail

WHERE

ID IN (

SELECT

fk

FROM

"public"."tb_village_lw_member"

WHERE

del_flag = '0'

AND (

duty LIKE '%党支部书记%'

OR duty LIKE '%党总支书记%'

)

AND duty NOT LIKE '%社区%'

GROUP BY

fk

HAVING

COUNT (1) > 1

);

5、统计每个城市软弱涣散村转化完成和未完成的数量

select

[城市]

sum(CASE WHEN org.weak_lax_status ='0' THEN 1 ELSE 0 END) as 未完成,

sum(CASE WHEN org.weak_lax_status ='1' THEN 1 ELSE 0 END) as 完成

from

    info

group by

    [城市]

order by 2