mysql复杂的join，有人吗？

Question

我需要写这样的查询，有什么帮助吗？

select id, parent, faq, 
(select count(*) from faq_table where parent = 
(select id from faq_questions) group by id) 
 as reply from faq_table

此表存储问题和答案(如 FAQ 之类的东西)，而答案在 parent 列中获取其问题 ID 的值。理想情况下，我还想添加一个名为 par 的第二列，其中所有问题的值都将为 1。

喜欢:

id  | parent | faq
19  |    0   | name a president of the US
20  |   19   | Bush
21  |   19   | Obama
22  |    0   | Can Canada win the WC match against the Lankan's today
23  |   22   | Yes because Cheema is going to make a double today

在该查询的结果表中，我必须得到:

id  | parent | faq  |  reply | par
19  |   0    | name a president of the US | 2 | 1
20  |  19    | Bush  | 0 | 0
21  |  19    | Obama | 0 | 0
22  |   0    | Can Canada win the WC match against the Lankan's today | 1 | 1
23  |  22    | Yes because Cheema is going to make a double today | 0 | 0

Answer 1

这仅适用于单个层次结构级别:

SELECT t.id, t.parent, t.faq, IFNULL(g.Cnt, 0) as reply,
  g.Cnt IS NOT NULL AS par
FROM faq_table t LEFT JOIN (
    SELECT parent, COUNT(*) Cnt
    FROM faq_table
    WHERE parent > 0
    GROUP BY parent
  ) g ON t.id = g.parent

否则，您可以使用子查询:

SELECT t.id, t.parent, t.faq,
  (SELECT COUNT(*) FROM faq_table f WHERE f.parent = t.id) as reply,
  (SELECT COUNT(*) FROM faq_table f WHERE f.parent = t.id) > 0 As par
FROM faq_table t