group by分組問題

Question

數據如下：    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)）得出的剩余數量

Answer 1

相同REQUEST_NO的QTY值是一样的吗?
select REQUEST_NO,AVG(QTY)-SUM(QTY_1) from 表名 group by REQUEST_NO

Answer 2

TRY IT ..
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> 

Answer 3

我是通過表連接得到的QYT_1，實際上結果還需要得出我列出的所有列，有沒有辦法？？

Answer 4

我最后想要得到這樣的數據：
    
    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有沒有能幫忙的？

Answer 5

select REQUEST_NO ,CDAY,PURCHASE_VENDORID ,DESCRIPTION  ,sum(QTY_1 )
from table group   REQUEST_NO ,CDAY,PURCHASE_VENDORID ,DESCRIPTION  

Answer 6

select REQUEST_NO ,CDAY,PURCHASE_VENDORID ,DESCRIPTION ,QTY  ,sum(QTY_1 ) 
from table group  REQUEST_NO ,CDAY,PURCHASE_VENDORID ,DESCRIPTION , QTY     

Answer 7

    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

Answer 8

select REQUEST_NO ,CDAY,PURCHASE_VENDORID ,DESCRIPTION ,QTY  ,sum(QTY_1 )
from PLON group BY REQUEST_NO ,CDAY,PURCHASE_VENDORID ,DESCRIPTION , QTY

Answer 9

這個問題實在是很擾民！～ 算了！ Group by不可以這樣執行的～