數據如下: REQUEST_NO CDAY PURCHASE_VENDORID DESCRIPTION QTY QTY_1
123456 2008.09.08 AAA 111*222 12 6
123456 2008.09.08 AAA 111*222 12 4
456123 2008.09.08 BBB 222*111 4 2
456123 2008.09.08 BBB 222*111 4 2想要求出只根據訂單號(REQUEST_NO)進行分組后數量減去數量1(QTY-SUM(QTY_1))得出的剩余數量
123456 2008.09.08 AAA 111*222 12 6
123456 2008.09.08 AAA 111*222 12 4
456123 2008.09.08 BBB 222*111 4 2
456123 2008.09.08 BBB 222*111 4 2想要求出只根據訂單號(REQUEST_NO)進行分組后數量減去數量1(QTY-SUM(QTY_1))得出的剩余數量
select REQUEST_NO,AVG(QTY)-SUM(QTY_1) from 表名 group by REQUEST_NO
SQL> SELECT DISTINCT
2 REQUEST_NO,
3 QTY-SUM(QTY_1) OVER(PARTITION BY REQUEST_NO)
4 FROM TABLE_NAME
13 ;REQUEST_NO QTY-SUM(QTY_1)OVER(PARTITIONBY
---------- ------------------------------
123456 2
456123 0SQL>
REQUEST_NO CDAY PURCHASE_VENDORID DESCRIPTION QTY QTY_1
123456 2008.09.08 AAA 111*222 12 10
456123 2008.09.08 BBB 222*111 4 4有沒有能幫忙的?
from table group REQUEST_NO ,CDAY,PURCHASE_VENDORID ,DESCRIPTION
from table group REQUEST_NO ,CDAY,PURCHASE_VENDORID ,DESCRIPTION , QTY
REQUEST_NO CDAY PURCHASE_VENDORID DESCRIPTION QTY QTY_1
123456 2008.09.08 AAA 111*222 12 6
123456 2008.09.08 AAA 111*200 12 4
456123 2008.09.08 BBB 200*111 4 2
456123 2008.09.08 BBB 222*111 4 2 只要以订单号分组,数量(QTY)不变,也就是说123456的数量还是12后面数量QTY_1得出为10,同理如456123
from PLON group BY REQUEST_NO ,CDAY,PURCHASE_VENDORID ,DESCRIPTION , QTY