我想要获得状态为已发货的订单的总产品数量.如何将其编写为单个SQL查询?
表1:
id | order_id | product_id | quantity |
---|---|---|---|
1 | 100001 | 123456780 | 3 |
2 | 100002 | 123456781 | 1 |
3 | 100002 | 123456782 | 5 |
4 | 100003 | 123456783 | 2 |
表2:
id | order_id | order_status | order_date |
---|---|---|---|
1 | 100001 | preparing | 2023-01-26 |
2 | 100001 | prepared | 2023-01-26 |
3 | 100001 | delivered | 2023-01-26 |
4 | 100002 | preparing | 2023-01-26 |
5 | 100002 | prepared | 2023-01-26 |
6 | 100002 | delivered | 2023-01-26 |
7 | 100003 | preparing | 2023-01-26 |
8 | 100004 | preparing | 2023-01-26 |
9 | 100001 | returned | 2023-01-27 |
下面的SQL查询未按预期工作.因为它不会查看订单的最新状态:
SELECT SUM(t1.quantity) AS total
FROM table_1 t1
LEFT JOIN table_2 t2
ON t1.order_id = t2.order_id
WHERE t2.order_status = 'delivered'
结果:9(3+1+5)结果不正确.因为返回的是订单号100001的最后状态,所以不应该将数字3加到总数中. 正确的结果应该是6(1+5).