一些简单的pgsql

 1.jsonb字段相当于in查询  格式：[{},{}]

SELECT *  
FROM my_table  
WHERE EXISTS (  
    SELECT 1  
    FROM jsonb_array_elements("label") AS elem(obj)  
    WHERE elem.obj ->> 'type' = ANY(ARRAY['电子信息', '另一个类型'])  
);

 2.json字段相当于=查询  格式：[{},{}]

SELECT *  
FROM my_table  
WHERE "label" @> '[{"type": "电子信息"}]';

3.string_agg(distinct qualified_name,',') qualified_name_str

4.

row_number() over(partition by t.entity_code

order by

t.create_time desc) as rn

5.

jsonb_agg(jsonb_build_object(

'chain_id_first', chain_id_first ,

'chain_level_first', chain_level_first,

'chain_id_second', chain_id_second ,

'chain_level_second', chain_level_second,

'chain_id_third', chain_id_third ,

'chain_level_third', chain_level_third,

'chain_id_fourth', chain_id_fourth ,

'chain_level_fourth', chain_level_fourth

)) industry_chain

6. 

CREATE TABLE b as (
select 
a.id
,a.subject_code company_code
,b.report_year
,b.project_name
,b.award_type
,b.award_rank
,b.award_item
,b.award_level
,b.province
from (select id,subject_code,subject_id from public.c where is_delete = 0 and subject_code is not null) a
left join (select * from public.a where is_delete = 0 ) b on b.id = a.subject_id
limit 10
)

7.GREATEST ( a.update_time, b.update_time, c.update_time) AS update_time 

8.change_date > NOW() - INTERVAL '2 months' 

 9.coalesce(utotal.userTotal, '0') as userNumTotal

 10.

when regcapital_amt_cal >= 50000000 then '5000万以上'

when 50000000 >= regcapital_amt_cal and regcapital_amt_cal > 10000000 then '1000-5000万'

when 10000000 >= regcapital_amt_cal and regcapital_amt_cal > 1000000 then '100-1000万'

when regcapital_amt_cal < 1000000 then '100万以下'

end as regcapital_section

11.

UPDATE public.zj_project

SET file = jsonb_set(

file,

'{0,url}',

to_jsonb(regexp_replace(file->0->>'url', '9000', '8000'))

)

WHERE file::text LIKE '%:9000/%';

 

UPDATE public.zj_project

SET file = REPLACE(file, '9000', '8081')

WHERE file::text LIKE '%:9000/%';

12.补充某个字段

CREATE OR REPLACE FUNCTION update_name_id()

RETURNS VOID AS $$

BEGIN

    UPDATE zj_flow.zj_project u

    SET company_code = n.company_code ,update_time = now()

    FROM zj_ads_pub.ads_deda_lget_company_info n

    WHERE u.investment_main_subject = n.company_name;

END;

$$ LANGUAGE plpgsql;

SELECT update_name_id();

 

13.

INSERT INTO zj_flow.zj_project (project_id, project_name,investment_main_subject,land_use_situation,project_introduction,project_address,file,add_time,update_time)

WITH max_id AS (

    SELECT COALESCE(MAX(project_id), 0) AS max_id

    FROM zj_flow.zj_project

),

numbered_rows AS (

    SELECT ROW_NUMBER() OVER () + max_id AS project_id,a.*,now() as add_time, now() as update_time

    FROM

    (select * from zj_flow.zj_project_v1) a

    CROSS JOIN max_id

    

)

SELECT *

FROM numbered_rows;

 

14.

SELECT id, string_agg(distinct data->>'name', ',') as result

, string_agg(distinct typeSecond->>'type_second', ',') as result1

FM sc.a,

sonb_array_elements(qualified_name) as data

,jsonb_array_elements(label) as typeSecond

GROUP BY id