Postgresql如何组合这两个查询？

Question

我试着把这两个查询结合起来，但我不知道

SELECT id FROM comments WHERE thread_id = $1 AND parent_id is NULL

这将返回一个整数。一.e-5

，然后将id结果(5)用于此递归查询。

With RECURSIVE recursetree AS
(
 SELECT * FROM comments WHERE comment_id = (use the id 5 here)
 UNION
 SELECT t.*
 From comments t
 JOIN recursetree rt ON rt.comment_id = t.parent_id                         
 )
 SELECT * from recursetree

Answer 1

您应该能够将第一个查询放入递归子查询的锚点：

With RECURSIVE recursetree AS (
      SELECT c.
      FROM comments c
      WHERE thread_id = $1 
      UNION ALL
      SELECT c.*
      FROM comments c JOIN
           recursetree
           rt ON rt.comment_id = c.parent_id                         
     )
SELECT * 
FROM recursetree;

还请注意：

• UNION ALL比UNION更有意义，除非您希望承担删除重复项的开销。
• 对于comments来说，表别名comments比任意字母t更有意义。
• $1建议您使用一个参数来处理查询字符串。您应该在查询中使用参数。