sql - SQL SELECT多个SUM()错误

Question

我在使用SUM()的SQL SELECT语句中涉及多个JOIN函数时遇到问题。

每当我将两个值求和时，它将使另一个求和函数中的值加倍。我该如何预防？

示例：SQL Fiddle-所有X和Y值均应为2。

我正在使用SQLite。

Answer 1

您可以为此使用UNION：

SELECT id, SUM(bamount) AS BAmount, SUM(camount) AS CAmount
FROM
(
  SELECT a.id, SUM(b.amount) AS bamount, 0  AS camount
  FROM a 
  LEFT JOIN b ON a.id = b.a_id 
  GROUP BY a.id
  UNION ALL
  SELECT a.id, 0, SUM(c.amount)  AS camount
  FROM a
  LEFT JOIN c ON a.id = c.a_id 
  GROUP BY a.id
) AS t
GROUP BY id;

updated demo

这将为您提供：

| id | BAmount | CAmount |
|----|---------|---------|
|  1 |       2 |       2 |
|  2 |       2 |       2 |
|  3 |       2 |       2 |