postgresql 多條記錄合併一條，或取最新的一條資料

木头左發表於2024-04-26
SQL

將一個表中的某一列的多行資料拼接成一行

一種方法

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;```



![](https://mutouzuo.oss-cn-hangzhou.aliyuncs.com/my/20200617110022324.png)


去掉重複的，增加distinct

```python
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;```



![](https://mutouzuo.oss-cn-hangzhou.aliyuncs.com/my/20200617110158403.png)


 

如果要排序，就要使用第二種方法,array_agg

```python
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;```


    



![](https://mutouzuo.oss-cn-hangzhou.aliyuncs.com/my/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1YW5zag==,size_16,color_FFFFFF,t_70)

![](https://mutouzuo.oss-cn-hangzhou.aliyuncs.com/my/2022010708182285224.gif)
​

 

如果要取最新的一條資料， 則要用row_number()

```python
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```



![](https://mutouzuo.oss-cn-hangzhou.aliyuncs.com/my/20200617115848782.png)


或者用(array_agg(person_name order by create_time desc ))[1]

```python
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;```



![](https://mutouzuo.oss-cn-hangzhou.aliyuncs.com/my/20200617120430692.png)

相關文章