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