# SUM和GROUP BY问题

PS：这是我使用的查询，当只有一个结算时，它会返回结算金额的双倍值：

``````SELECT
d.id,
SUM(o.goods_amount) AS amount,
SUM(s.amount) AS settlementAmount,
FROM delivery d
LEFT JOIN order o
ON d.id = o.delivery_id
LEFT JOIN settlement s
ON d.id = s.delivery_id
WHERE d.id = *deliveryId*
GROUP BY d.id;
``````

``````Delivery Table
ID  Delivered_On
1   2016-07-01
2   2016-07-02

Orders Table
id  Deliveries_id  Amount
1   1              100
2   1              200
3   1              300
4   2              75

Settlements Table
id  Delivery_id   Amount
1   1             525   (explicitly wrong amount to show result)
2   1             75
``````

``````SELECT
d.id AS delivery_id,
sumOrd.Amount as OrderAmount,
sumStl.Amount as SettlementAmount
from
delivery d

LEFT JOIN
( select
d2.id,
SUM(o.amount) AS OrderAmount
from
delivery d2
JOIN order o
ON d2.id = o.deliveries_id
WHERE
d2.delivered_on >= '2016-06-10'
and d2.delivered_on < '2016-06-11'
GROUP BY
d2.id ) sumOrd
on d.id = sumOrd.id

LEFT JOIN
( select
d2.id,
SUM(s.amount) AS SettlementAmount
from
delivery d2
JOIN Settlement s
ON d2.id = s.delivery_id
WHERE
d2.delivered_on >= '2016-06-10'
and d2.delivered_on < '2016-06-11'
GROUP BY
d2.id ) sumStl
on d.id = sumStl.id
where
d.delivered_on >= '2016-06-10'
and d.delivered_on < '2016-06-11'
``````

0 条评论