数据库查询简单、高效的小技巧

查询按某一维度查询最近发生的一次

例如：所有用户的最近一次登入，平台收到最后的一次记录等

select user_id，max(login_time) as login_time from legion_table group by user_id;

获取某一字段最短的一行数据

SELECT *
from tb_region
order by length(region_path)
limit 1

json,jsonb 数据的联表查询

select t1.feild1,  t2.v
from a_table as t1, jsonb_each(jsonb_feild->’a’) as t2

json,jsonb 数据的联表查询

select t1.feild1,  t2.a, t2.b
from a_table as t1, jsonb_to_recordset(jsonb_feild) as t2(a int, b int)

没有数据显示为0

select count(*) , feild
from a_table
group by field

表1字段code用逗号分割，表2字段为code和name两列
将表1的字段code分割字段，查询出转换成按表2的name分割

CREATE TABLE public.tb_test_staff (
    phone varchar(64) NOT NULL,
    staff_name varchar(64) NOT NULL,
    project_id text NULL,
    CONSTRAINT pk_tb_tb_test_staff PRIMARY KEY (phone)
); 

INSERT INTO public.tb_test_staff (phone,staff_name,project_id) VALUES 
('13708339129','葛涛','17,538,18,0')
,('18623313986','王东梅','17,538,0,18')
,('15123212210','唐冬玲','0,17,556,538,0,18'); 

CREATE TABLE public.tb_organization (
    org_uuid varchar(48) NOT NULL,  org_name varchar(32) NULL );

INSERT INTO public.tb_organization
(org_uuid, org_name)
VALUES('556', '第一城区');
INSERT INTO public.tb_organization
(org_uuid, org_name)
VALUES('17', '郑州西耿河');
INSERT INTO public.tb_organization
(org_uuid, org_name)
VALUES('18', '郑州金科城');
INSERT INTO public.tb_organization
(org_uuid, org_name)
VALUES('538', '郑州世纪景园');
INSERT INTO public.tb_organization
(org_uuid, org_name)
VALUES('qq', '世纪'); 

select
    b1.phone ,
    string_agg(b2.org_name, ',')
from
    (
    select a1.phone,
        case
            when project_id_ref= '0' then 'qq'
            else project_id_ref
        end
    from tb_test_staff as a1,
    regexp_split_to_table(a1.project_id, ',') as project_id_ref
    ) as b1,
    tb_organization as b2
where
    b1.project_id_ref= b2.org_uuid
group by
    b1.phone

查找最新的按某一字段区分的不重复的数据 （因为area_id作为区分，名称有可能会变，选择最新的名称显示）

select distinct code,name  from (
select max(create_time),
first_value(area_name) over (partition by area_id order by max(create_time) desc) as name,
area_id as code
 from tb_passenger_density_model 
where camera_id = 'aaaaa'
group by area_id,area_name
) as b

查询某一字段不同值最新的一条数据

CREATE TABLE public.agg_test (
    id1 text NULL,
    id2 text NULL,
    value int4 NULL,
    update_time timestamp NULL
);

INSERT INTO public.agg_test (id1,id2,value,update_time) VALUES 
('1','1',1,'2022-01-01 00:00:00.000')
,('1','1',2,'2021-12-11 00:00:00.000')
,('2','2',3,'2022-01-01 00:00:00.000')
;

select distinct on (ID2) ID2, VALUE, update_time
from agg_test
order by ID2,update_time DESC