sql - 如何在不交叉的情况下组合两个 LEFT JOINS？

Question

我有两个查询我想合并为一个，但是当我这样做时要保持矩阵效应。
SELECT u.*, COUNT(q.id)
FROM users u
LEFT JOIN questions q ON u.id = q.author_id
GROUP BY u.idSELECT u.*, COUNT(a.id)
FROM users u
LEFT JOIN answers a ON u.id = a.author_id
GROUP BY u.id
这些查询本身给了我 10 个问题和 10 个答案。
当我像下面这样组合它们时，问题和答案的数量会跳到 100 个。
SELECT u.*, COUNT(q.id), COUNT(a.id)
FROM users u
LEFT JOIN questions q ON u.id = q.author_id
LEFT JOIN answers a ON u.id = a.author_id
GROUP BY u.id

Answer 1

你只需要添加不同的计数

SELECT u.*, COUNT(DISTINCT q.id), COUNT(DISTINCT a.id)
 FROM users u
 LEFT JOIN questions q ON u.id = q.author_id
 LEFT JOIN answers a ON u.id = a.author_id
 GROUP BY u.id

Here's a demo使用 Data.SE 进行操作
或者，您可以在 from 子句中使用内联 View

SELECT u.*, q.QuestionCount, a.AnswerCount
FROM   users u 
       LEFT JOIN (SELECT Count(id) QuestionCount, 
                         author_id 
                  FROM   questions 
                  GROUP  BY author_id) q 
              ON u.id = q.author_id 
       LEFT JOIN (SELECT Count(id) AnswerCount, 
                         author_id 
                  FROM   answers 
                  GROUP  BY author_id) a 
              ON u.id = q.author_id 

Demo