pgsql 数组格式的查询

 

查询各连锁的标签 

select a.id,a.name,b.name as label from client as a
left join label b
on b.id = any(a.label) 
where a.category='6' and b.parentid=1

如果有多个标签，可以用array_add()

select a.id,a.name,array_agg(b.name) as label from client as a
left join label b
on b.id = any(a.label) 
where a.category='6' 
group by a.id,a.name

select a.id,a.name,array_agg(b.name) as label from client as a
left join label b
on b.id = any(a.label) 
where a.category='6' and label is not null
group by a.id,a.name

 

将数组转换为字符串  array_to_string(array_agg(d.name),',') 

select t.parentid as id,a.name,grade,b.name as 小类,c.name as 大类,t.门店数,array_to_string(array_agg(d.name),',') as label,regioncode,regionname
                from client as a inner join  
                (select parentid,count(parentid) as 门店数 from client where parentid>0 group by parentid)
                as t
                on a.id=t.parentid
                inner join client_category as b on a.level1=b.code
                inner join client_category as c on a.category=c.code
                left join label as d on d.id= any(a.label)      
                where 门店数>2
                group by t.parentid ,a.name,grade,b.name,c.name,t.门店数,regioncode,regionname
                order by 门店数 desc