我想获得一组唯一的id作为数组。
示例:
SELECT a.*, b.*, array_agg(a.id) OVER() as groups_ids
FROM author a
JOIN books as b
此时,我得到多个相同的author.id
,因为许多books
属于author
。
如何只获取不同的id?
谢谢。
一种方法是不使用窗口函数:
SELECT a.*, b.*, array(select distinct a2.id from author a2) as groups_ids
FROM author a
JOIN books as b on a.id = b.author_id;