SQL查询返回与逗号分隔字符串具有相同ID的元素

Question

我有两个表，table1有一个entry_ID，entry_date等入口信息。 table2 有 entry_ID 和 entry_subject。每个 entry_ID 可以有任意多个 entry_subjects。

我想要一个将返回 entry_ID、entry_date 以及与该条目相对应的主题列表(以逗号分隔)的查询。

第一步似乎只是获取一个返回 entry_ID 和来自 table2 的逗号分隔主题列表的查询。一旦我知道加入应该很容易。
我改编了 recursive CTE method from this site : 适合我的情况:

WITH RECURSIVE CTE (entry_ID, subjectlist, subject, length)
    AS ( SELECT entry_ID, cast( '' as varchar(8000))
                        , cast( '' as varchar(8000)), 0
         FROM table2 
         GROUP BY entry_ID
         UNION ALL 
         SELECT t2.entry_ID, 
             cast(subjectlist || CASE length = 0 THEN '' ELSE ', ' END
                              || entry_subject AS varchar(8000) ),
             cast (t2.entry_subject as varchar(8000)),
             length +1
         FROM CTE c 
         INNER JOIN table2 t2 
             on c.entry_ID=t2.entry_ID where t2.entry_subject > c.subject)
SELECT entry_ID, subjectlist FROM (
    SELECT entry_ID, subjectlist, RANK() OVER (
        PARTITION BY entry_ID order by length DESC)
    FROM CTE) D (entry_ID, subjectlist, rank) where rank = 1;

它有效，我得到了我期望的响应。为了实现我的最终目标，我使用的查询是这样的:

SELECT t1.* t2.subjectlist FROM table1 
    JOIN (ABOVE QUERY) AS t2 on t1.entry_ID=t2.entry_ID; 

这看起来很笨拙。这真的是最好的方法吗？

Answer 1

如果我理解正确，那么应该有一个简单得多的解决方案。

测试设置

根据您的描述 - 您本可以为我们做到这一点:

CREATE TABLE table1 (
   entry_id int4 PRIMARY KEY
 , entry_date date
);

CREATE TABLE table2 (
   entry_id int4 REFERENCES table1 (entry_id)
 , entry_subject text
 , PRIMARY KEY (entry_id, entry_subject)
);

INSERT INTO table1 VALUES (1, '2011-09-01'), (2, '2011-09-02'),(3, '2011-09-03');
INSERT INTO table2 VALUES (1, 'foo1'), (2, 'foo2'), (2, 'bar2')
                        , (3, 'foo3'), (3, 'baz3'), (3, 'bar3');  

回答

string_agg() 需要 Postgres 9.0+

SELECT t1.entry_id, t1.entry_date
     , string_agg(t2.entry_subject, ', ') AS entry_subjects
FROM   table1 t1
JOIN   table2 t2 USING (entry_id)
GROUP  BY 1,2
ORDER  BY 1;

 entry_id | entry_date | entry_subjects
----------+------------+------------------
        1 | 2011-09-01 | foo1
        2 | 2011-09-02 | bar2, foo2
        3 | 2011-09-03 | baz3, bar3, foo3

或者，如果您希望对 entry_subjects 排序:

SELECT DISTINCT ON (1)
       t1.entry_id
     , t1.entry_date
     , string_agg(t2.entry_subject, ', ') OVER (
          PARTITION BY t1.entry_id ORDER BY t2.entry_subject
          RANGE BETWEEN UNBOUNDED PRECEDING
                    AND UNBOUNDED FOLLOWING) AS entry_subjects
  FROM table1 t1
  JOIN table2 t2 USING (entry_id)
  ORDER BY 1;

 entry_id | entry_date | entry_subjects
----------+------------+------------------
        1 | 2011-09-01 | foo1
        2 | 2011-09-02 | bar2, foo2
        3 | 2011-09-03 | bar3, baz3, foo3

您可以对 table2 上的子选择执行相同的操作，首先是 ORDER BY entry_subject。