时间戳

首页 > 解决方案 > 如何将子查询结果合并到每行另一个子查询的结果？

oracle11g - 如何将子查询结果合并到每行另一个子查询的结果？

问题描述

我正在努力寻找一种方法来获得我想要的输出。这是让我很难的子查询。需要一些帮助。

TABLE: Sales
DATE        INVOICE#   AMT      TYPE       
01-DEC-20   10001      50.00    TAKEAWAY   
01-DEC-20   10002      50.00    DELIVERY   
01-DEC-20   10003      50.00    DINEIN     
01-DEC-20   10004      50.00    TAKEAWAY   
01-DEC-20   10005      50.00    DELIVERY   
01-DEC-20   10006      50.00    DINEIN     
02-DEC-20   10001      100.00   TAKEAWAY   
02-DEC-20   10002      100.00   DELIVERY   
02-DEC-20   10003      100.00   DINEIN     
02-DEC-20   10004      100.00   TAKEAWAY   
02-DEC-20   10005      100.00   DELIVERY   
02-DEC-20   10006      100.00   DINEIN

以下是我想要的输出报告。

DATE        TOTAL_SALES    TYPE       SUBTOTAL_TYPE   PERC(SUBTOTAL / TOTAL)
01-DEC-20   300            TAKEAWAY   100             33.3%
01-DEC-20   300            DELIVERY   100             33.3%
01-DEC-20   300            DINEINE    100             33.3%
02-DEC-20   600            TAKEAWAY   200             33.3%
02-DEC-20   600            DELIVERY   200             33.3%
02-DEC-20   600            DINEIN     200             33.3%

所以基本上，我正在努力解决的是 TOTAL_SALES 列。

标签： oracle11goracle-apex

解决方案

这对我有用 - 我在 18.1 db 上进行了测试，但我相信这个功能已经在 11g 中

WITH test_data(dt, invoice, amt, type)
AS
(
SELECT '01-DEC-20',   '10001'      ,50    ,'TAKEAWAY' FROM DUAL  UNION ALL
SELECT '01-DEC-20',   '10002'      ,50    ,'DELIVERY' FROM DUAL  UNION ALL
SELECT '01-DEC-20',   '10003'      ,50    ,'DINEIN' FROM DUAL  UNION ALL
SELECT '01-DEC-20',   '10004'      ,50    ,'TAKEAWAY' FROM DUAL  UNION ALL
SELECT '01-DEC-20',   '10005'      ,50    ,'DELIVERY' FROM DUAL  UNION ALL
SELECT '01-DEC-20',   '10006'      ,50    ,'DINEIN' FROM DUAL  UNION ALL
SELECT '02-DEC-20',   '10001'      ,100   ,'TAKEAWAY' FROM DUAL  UNION ALL
SELECT '02-DEC-20',   '10002'      ,100   ,'DELIVERY' FROM DUAL  UNION ALL
SELECT '02-DEC-20',   '10003'      ,100   ,'DINEIN' FROM DUAL  UNION ALL
SELECT '02-DEC-20',   '10004'      ,100   ,'TAKEAWAY' FROM DUAL  UNION ALL
SELECT '02-DEC-20',   '10005'      ,100   ,'DELIVERY' FROM DUAL  UNION ALL
SELECT '02-DEC-20',   '10006'      ,100   ,'DINEIN'  FROM DUAL
)
SELECT 
 distinct dt,
SUM(AMT) OVER (PARTITION BY dt) as total_sales,
type,
SUM(AMT) OVER (PARTITION BY dt,type) as subtotal_type,
to_char(SUM(AMT) OVER (PARTITION BY dt,type)/SUM(AMT) OVER (PARTITION BY dt)*100,'990.00')||'%'
FROM test_data
ORDER BY dt;

推荐阅读