customer C
left join customer_territory ct on ct.customer = c.id
left join user_territory ut on ct.territory = ut.territory
left join user_info u on ut.user_info = u.id
WHERE
C.is_deleted = FALSE
and ct.is_deleted = false
and ut.is_deleted = false
and u.is_deleted = false
and u.enable = true
AND C.is_active = TRUE
AND C.record_type IN (
'hco',
'pharmacy',
'distributor')
group by C.ID
进阶版，通过having语句摘出来所有出现过两次以上的
SELECT
C.ID,
string_agg(u.name::varchar,','),
count(C.ID) as cnt
customer C
left join customer_territory ct on ct.customer = c.id
left join user_territory ut on ct.territory = ut.territory
left join user_info u on ut.user_info = u.id
WHERE
C.is_deleted = FALSE
and ct.is_deleted = false
and ut.is_deleted = false
and u.is_deleted = false
and u.enable = true
AND C.is_active = TRUE
AND C.record_type IN (
'hco',
'pharmacy',
'distributor')
group by C.ID
HAVING count(C.ID)>=2
通过id列来聚合belong_user_saved列，应用string_agg函数，只要id一样则把第二列通过逗号连接起来聚合前：聚合后： SELECT C.ID, string_agg(u.name::varchar,',') belong_user_savedFROM customer C left join customer_territory ct on...
with person_name as (
select c.
id
,
array_to_
string
(array_
agg
(distinct p.c_name), ' , ') as p_name
from biz_notification_config c
join biz_notification_person p
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'
问题介绍：自连接的内部计算机制：第一步：生成同一张表格的笛卡尔积。有序数对：有序数对指的是<1,2>，无序数对指的是{1，2}。对于有序数对，两组
数据
交换意义不同。即<1,2> 不等于<2,1>。而无序数对{1，2} = {2，1}。SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1, Pro...
1.
多行
合并
成
一行
//方式一：使用
string
_
agg
函数
,直接把
一个
表达式变成字符串
SELECT
string
_
agg
(DISTINCT fieldname, ',' order by fieldname ASC) FROM table;
//方式二：使用array_
agg
函数
,,把表达式变成
一个
数组,一般配合 array_to_
string
()
函数
使用
SELECT array_to_
string
(array_
agg
(DISTINCT fieldname order by
【转发原因：
pg
sql
查询结果，需要添加一列作为唯一标识，该排序方法正好可以解决！】
【文章链接：https://blog.csdn.net/kmblack1/article/details/79759995】
版权声明：本文为博主原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/kmblack1/ar...
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION address_standardizer;
