sql - 无法在报告中计算平均天数？

Question

你好，我正在尝试编写一个返回此报告的 sql 语句:

-------- Supplier's Order Status Report -------- Page:


Supplier Supplier                  Order Order    Receive     Number
  Code Name                        No. Date     Date       of Days STATUS
---------- -------------------- ---------- -------- -------- --------- --------
   101 ACE Auto                   1000 04/03/15 04/06/15      3.00 Complete
                                  1001 04/03/15 04/05/15      2.00 Complete
                                  1002 04/04/15 04/05/15      1.00 Complete
                                  1003 04/04/15 04/05/15      1.00 Complete
                                  1004 04/03/15 04/06/15      3.00 Complete
                                  1013 04/13/15                    Open
********** ********************                              ---------
Avg Days                                                          2.00

   102 Better Auto Buys           1005 04/04/15 04/04/15       .00 Complete
                                  1006 04/03/15 04/06/15      3.00 Complete
                                  1010 04/13/15                    Open
                                  1011 04/13/15 04/13/15       .00 Complete
********** ********************                              ---------
Avg Days                                                          1.00

   103 Cars R Us                  1007 04/05/15 04/07/15      2.00 Complete
                                  1012 04/13/15                    Open
********** ********************                              ---------
Avg Days                                                          2.00

   104 Delta Parts                1008 04/05/15 04/06/15      1.00 Complete
                                  1009 04/03/15 04/05/15      2.00 Complete
********** ********************                              ---------
Avg Days                                                          1.50

这是我的:

SET LINESIZE 100
TTITLE CENTER "------SUPPLIER'S ORDER STATUS REPORT------- Page:"

COLUMN SUPPLIER_CODE HEADING "Supplier|Code"
COLUMN SUPPLIER_NAME HEADING "Supplier|Name"
COLUMN ORD_NUM HEADING "Order|No."
COLUMN ORD_DATE HEADING "Order|Date"
COLUMN ORD_RECDATE HEADING "Recieve|Date"

BREAK ON SUPPLIER_CODE ON SUPPLIER_NAME 
SELECT S.SUPPLIER_CODE, SUPPLIER_NAME, ORD_NUM, ORD_DATE, ORD_RECDATE,   (ORD_RECDATE-ORD_DATE) AS "Number|of Days", DECODE(ORD_RECDATE, NULL, 'OPEN','COMPLETE') AS STATUS
FROM SUPPLIER S, ORD O
WHERE S.SUPPLIER_CODE = O.SUPPLIER_CODE
ORDER BY S.SUPPLIER_CODE;

我的问题是每当我尝试计算平均天数时，在 sqlplus 或 sql developer 中什么都没有发生？ p.s 我知道它不在我遗漏的代码中。

Answer 1

将 AVG(ORD_RECDATE-ORD_DATE) OVER (PARTITION BY s.supplier_code) AS avg_days 添加到您的 SELECT 子句