派生列的动态列名 - ORACLE SQL

Question

我正在尝试使用"从双重中选择"在查询上设置动态列名称。 这可能吗？如果没有，请为我推荐替代方案来实现这一点。 我在普通选择查询中需要它，而不是通过使用存储过程。

我正在尝试实现以下查询：

SELECT  A.NO
,A.SUB_NO
,A.DCY
,A.STATE
,NVL(TO_CHAR(M1.NUMERATOR),'0') AUG_NUM     --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_NUM' FROM DUAL
,NVL(TO_CHAR(M1.DENOMINATOR),'0') AUG_DEN   --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_DEN' FROM DUAL
,NVL(TO_CHAR(M2.NUMERATOR),'0') JUL_NUM     --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_NUM' FROM DUAL
,NVL(TO_CHAR(M2.DENOMINATOR),'0') JUL_DEN   --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_DEN' FROM DUAL
,NVL(TO_CHAR(M3.NUMERATOR),'0') JUN_NUM     --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_NUM' FROM DUAL
,NVL(TO_CHAR(M3.DENOMINATOR),'0') JUN_DEN   --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_DEN' FROM DUAL
,M1.M1_CALC
,M2.M2_CALC
,M3.M3_CALC
FROM A, M1,M2,M3;

提前感谢您的帮助。

Answer 1

我很确定最接近您想要的东西是使用UNION ALL。

SELECT NULL no,
NULL sub_no,
NULL dcy,
NULL state,
TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_NUM' AUG_NUM,
TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_DEN' AUG_DEN,
TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_NUM' JUL_NUM,
TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_DEN' JUL_DEN,
TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_NUM' JUN_NUM,
TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_DEN' JUN_DEN,
NULL M1_CAL,
NULL M2_CALC,
NULL M3_CALC
FROM dual
UNION ALL
SELECT  A.NO
,A.SUB_NO
,A.DCY
,A.STATE
,NVL(TO_CHAR(M1.NUMERATOR),'0') AUG_NUM     --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_NUM' FROM DUAL
,NVL(TO_CHAR(M1.DENOMINATOR),'0') AUG_DEN   --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_DEN' FROM DUAL
,NVL(TO_CHAR(M2.NUMERATOR),'0') JUL_NUM     --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_NUM' FROM DUAL
,NVL(TO_CHAR(M2.DENOMINATOR),'0') JUL_DEN   --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_DEN' FROM DUAL
,NVL(TO_CHAR(M3.NUMERATOR),'0') JUN_NUM     --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_NUM' FROM DUAL
,NVL(TO_CHAR(M3.DENOMINATOR),'0') JUN_DEN   --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_DEN' FROM DUAL
,M1.M1_CALC
,M2.M2_CALC
,M3.M3_CALC
FROM A, M1,M2,M3;

Answer 2

WITH A AS
(
SELECT  
636 "NO", 159 "SUB_NO", To_Char(SYSDATE, 'yyyy') "DCY", 'State 1' "STATE"
FROM dual       --This could be records from any of your data tables
UNION
SELECT  
1272 "NO", 318 "SUB_NO", To_Char(SYSDATE, 'yyyy') "DCY", 'State 2' "STATE"
FROM dual       --This could be records from any of your data tables
)
SELECT  
NO, SUB_NO, DCY, STATE,
MONTH_MINUS_2_NUM, MONTH_MINUS_2_DEN,
MONTH_MINUS_3_NUM, MONTH_MINUS_3_DEN,
MONTH_MINUS_4_NUM, MONTH_MINUS_4_DEN,
M1_CALC, M2_CALC, M3_CALC
FROM 
a
MODEL
DIMENSION BY( DCY, STATE)
MEASURES( NO, SUB_NO,
CAST('x' as VarChar2(20)) as MONTH_MINUS_2_NUM, 
CAST('x' as VarChar2(20)) as MONTH_MINUS_2_DEN,
CAST('x' as VarChar2(20)) as MONTH_MINUS_3_NUM,
CAST('x' as VarChar2(20)) as MONTH_MINUS_3_DEN,
CAST('x' as VarChar2(20)) as MONTH_MINUS_4_NUM,
CAST('x' as VarChar2(20)) as MONTH_MINUS_4_DEN,
0 as M1_CALC,
0 as M2_CALC,
0 as M3_CALC
)
RULES
(
MONTH_MINUS_2_NUM[ANY, ANY] = TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_NUM',
MONTH_MINUS_2_DEN[ANY, ANY] = TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_DEN',
MONTH_MINUS_3_NUM[ANY, ANY] = TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_NUM',
MONTH_MINUS_3_DEN[ANY, ANY] = TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_DEN',
MONTH_MINUS_4_NUM[ANY, ANY] = TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_NUM',
MONTH_MINUS_4_DEN[ANY, ANY] = TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_DEN',
-- Dynamic Calculations -> you can calculate prety much anything you want
M1_CALC[2022, 'State 1'] = NO[CV(), CV()] / 2,
M2_CALC[2022, 'State 1'] = NO[CV(), CV()] / 3,
M3_CALC[2022, 'State 1'] = NO[CV(), CV()] / 4,
M1_CALC[2022, 'State 2'] = NO[CV(), CV()] / 2,
M2_CALC[2022, 'State 2'] = NO[CV(), CV()] / 3,
M3_CALC[2022, 'State 2'] = NO[CV(), CV()] / 4
)