postgresql 多条记录合并一条，或取最新的一条数据

偏执的太偏执、 2021-07-26 15:22 2047阅读 0赞

将一个表中的某一列的多行数据拼接成一行

一种方法

SELECT person_id AS job_no,
           string_agg(person_name,',') as str_person_name
           
    FROM public.tb_attendance_model
    WHERE create_time >= '2019-06-17 00:00:00.000000'
      AND create_time < '2020-06-17 00:00:00.000000'
    GROUP BY person_id        
    ORDER BY str_person_name DESC
    LIMIT 10000;

![20200617110022324.png][]

去掉重复的，增加distinct

SELECT person_id AS job_no,
           string_agg(distinct(person_name),',') as str_person_name
           
    FROM public.tb_attendance_model
    WHERE create_time >= '2019-06-17 00:00:00.000000'
      AND create_time < '2020-06-17 00:00:00.000000'
    GROUP BY person_id        
    ORDER BY str_person_name DESC
    LIMIT 10000;

![20200617110158403.png][]

如果要排序，就要使用第二种方法,array\_agg

SELECT person_id AS job_no,
           string_agg(distinct(person_name),',') as str_person_name,
           array_to_string(array(select unnest(array_agg(person_name  order by create_time desc ))),',') as arr_person_name
    FROM public.tb_attendance_model
    WHERE create_time >= '2019-06-17 00:00:00.000000'
      AND create_time < '2020-06-17 00:00:00.000000'
    GROUP BY person_id        
    ORDER BY str_person_name DESC
    LIMIT 10000;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1YW5zag_size_16_color_FFFFFF_t_70][]![wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw==][wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw]

如果要取最新的一条数据， 则要用row\_number()

select job_no, person_name from (
    SELECT person_id AS job_no,	
    	person_name,
           row_number() over(partition by person_id order by create_time desc) as ranks
    FROM public.tb_attendance_model tam
    WHERE tam.create_time >= '2019-06-17 00:00:00.000000'
      AND tam.create_time < '2020-06-17 00:00:00.000000'     
    ORDER BY job_no DESC
    LIMIT 10000) tb where ranks=1

![20200617115848782.png][]

或者用(array\_agg(person\_name order by create\_time desc ))\[1\]

SELECT person_id AS job_no,
           string_agg(distinct(person_name),',') as str_person_name,
           (array_agg(person_name order by create_time desc ))[1] as arr_person_name
    FROM public.tb_attendance_model
    WHERE create_time >= '2019-06-17 00:00:00.000000'
      AND create_time < '2020-06-17 00:00:00.000000'
    GROUP BY person_id        
    ORDER BY str_person_name DESC
    LIMIT 10000;

![20200617120430692.png][]

[20200617110022324.png]: /images/20210726/f568f0d6d68c4c16a42790e49837502f.png
[20200617110158403.png]: /images/20210726/a4846636da4d4985aea4ef109b74d7c2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1YW5zag_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200617112555661.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1YW5zag==,size_16,color_FFFFFF,t_70
[wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw]: 
[20200617115848782.png]: /images/20210726/50d4e973b7b1426697cf4368c101118b.png
[20200617120430692.png]: /images/20210726/54242f0675b04c64b79bf896a81bdfdb.png