我有两个表,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;这似乎很笨拙。这真的是最好的方式吗?