我是SQL的新手,尝试获取订单号和收入的输出,其中与订单号相关的所有PO编号均已发货或准备发货。我的表称为订单,如下所示:
PO_NUM | ORDER_NUM |状态|收入
101 | 001 |生产中| 1.00
102 | 001 |已发货1.00
103 | 001 |已发货1.00
104 | 001 |准备发货| 1.00
201 | 002 |生产中| 1.00
202 | 002 |生产中| 1.00
203 | 002 |生产中| 1.00
301 | 003 |准备发货| 1.00
401 | 004 |已发货1.00
402 | 004 |已发货1.00
403 | 004 |已发货1.00
501 | 005 |准备发货| 1.00
502 | 005 |已发货1.00
503 | 005 |已发货1.00
基于此表的输出如下所示:
ORDER_NUM |收入
003 | 1.00
004 | 3.00
005 | 3.00
我敢肯定这可能很简单,但我无法弄清楚。请帮忙。
编辑:我只想返回与订单号相关的所有PO编号的状态已经发货或准备发货的订单号。
最佳答案
有很多方法可以做到这一点,但这是其中一种:
SELECT ORDER_NUM, REVENUE
FROM Orders
WHERE PO_NUM IN(
SELECT TOTALORDERS.PO_NUM
FROM
(SELECT PO_NUM, count(*) as TOTAL
FROM Orders
GROUP BY PO_NUM) as TOTALORDERS
INNER JOIN
(SELECT PO_NUM, count(*) as PROCESSED
FROM Orders
WHERE [STATUS] IN('Shipped','Ready to Ship')
GROUP BY PO_NUM) as PROCESSEDORDERS
ON TOTALORDERS.PO_NUM=PROCESSEDORDERS.PO_NUM
AND TOTALORDERS.TOTAL=PROCESSEDORDERS.PROCESSED)