我是SQL的新手,并试图获得订单号和收入的输出,其中所有与订单号相关的PO号已发货或准备发货。我的表名为Orders,看起来像这样:
PO_NUM | ORDER_NUM | STATUS | REVENUE
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 | REVENUE
03 | 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)