我有一个表,它是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
我想为每个表_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
有人帮我吗?感谢
我没有测试它,但它应该可以工作:
$sqlQuery = "
SELECT *, SUM(`bill`) AS `total_bill`
FROM `table1`
LEFT JOIN `table2`
ON table1.table1_id = table2.table2_id
GROUP BY `table1_id`
ORDER BY `total_bill` DESC
";
$result = mysql_query($sqlQuery);
while($row = mysql_fetch_array($result))
echo "{$row['table1_id']} - {$row['total_bill']}<br>";
SELECT SUM(bill) AS total_bill
from table1, table2
WHERE table1.table1_id=table2.table2_id
AND table2_id=$table1_id
Group by table1_id
order by total_bill
这应该会奏效,你需要小组通过才能得到一个以上的结果。
按以下方式尝试。它应该起作用。如果对某个特定列使用聚合函数,则该列应添加到groupby子句中。
SELECT SUM(bill) AS total_bill
from table1, table2
WHERE table1.table1_id=table2.table2_id
AND table2_id= yourId
Group by table1_id
order by bill;