php - 在 MySQL 和 PHP 上使用 Left Join 对其他表中的字段求和

Question

我有四个表:Table_A、Table_B、Table_C 和 Table_D。

TABLE_A
id_a | Name
A1   | ASD
A2   | ZXC

TABLE_B
id_b | id_a
B1   | A1
B2   | A2

TABLE_C
id_c | id_b | Value
C1   | B1   | 1
C2   | B1   | 1
C3   | B2   | 1
C4   | B2   | 1
C5   | B2   | 1

TABLE_D
id_d | id_a | Bill
D1   | A1   | 5
D2   | A2   | 10

我想为每个 table_a.id_a 获取 SUM(table_c.value) 作为 tot1 和 SUM(table_d.bill) 作为 tot2，如下所示:

id_a  | SUM_VALUE of table_c | SUM_BILL of table_d
A1    | 2                    | 5
A2    | 3                    | 10

我正在使用这个脚本:

$sql=" SELECT *, SUM(table_c.value) as tot1, SUM(table_d.bill) as tot2
FROM table_a
LEFT JOIN table_b ON table_b.id_a=table_a.id_a
LEFT JOIN table_c ON table_c.id_b=table_b.id_b
GROUP BY id_a ";

但是得到错误的结果:

id_a  | SUM_VALUE of table_c | SUM_BILL of table_d
A1    | 2                    | 10
A2    | 3                    | 30

有什么想法吗？

Answer 1

您可以在加入前进行求和计算:

SELECT a.id_a, b.sum_b, c.sum_c
FROM   table_a a
JOIN   table_b b ON a.id_a = b.id_b
JOIN   (SELECT   id_b, SUM(value)
        FROM     table_c
        GROUP BY id_a) c ON b.id_b = c.id_b
JOIN   (SELECT   id_a, SUM(value)
        FROM     table_d
        GROUP BY id_a) c ON a.id_a = d.id_b