查询中有大量的代码，如何减少Oracle SQL中的代码长度?

Question

sql专家在这种情况下有什么用?

我需要调用一些查询31次，这是大量的代码，是否可以使用一些好的选项来减少代码长度

我有31天列表my_days +其他列

。

with cte as
(
Select  day_1 as DAY_TOTAL, 1 as DAY, t1.column_1, t2.column_2, t3.column_3 from my_days my
left outer join table_1 t1 ....
left outer join table_2 t2 ....
left outer join table_3 t3 .... 
where user = :PARAMETER_USER and CODE =  :PARAMETER_CODE
union all
Select  day_2 as DAY_TOTAL, 2 as DAY, t1.column_1, t2.column_2, t3.column_3 from my_days my
left outer join table_1 t1 ....
left outer join table_2 t2 ....
left outer join table_3 t3 .... 
where user = :PARAMETER_USER and CODE =  :PARAMETER_CODE
---------
Select  day_31 as DAY_TOTAL, 3 as DAY, t1.column_1, t2.column_2, t3.column_3 from my_days my
left outer join table_1 t1 ....
left outer join table_2 t2 ....
left outer join table_3 t3 ....
where user = :PARAMETER_USER and CODE =  :PARAMETER_CODE
)
Select * from cte  + other join.....

也许创建函数和使用函数而不是查询联合所有?

Answer 1

您可以UNPIVOT:

SELECT day_total, day, column1, column2, column3
FROM   (
SELECT /*other_columns,*/ day_total, day
FROM   my_days
UNPIVOT (
day_total FOR day IN (
day_1 AS 1,
day_2 AS 2,
day_3 AS 3,
day_4 AS 4,
-- ...
day_29 AS 29,
day_30 AS 30,
day_31 AS 31
)
)
) my
left outer join table_1 t1 ....
left outer join table_2 t2 ....
left outer join table_3 t3 .... 
+ other join ...
WHERE  "USER" = :PARAMETER_USER
AND    CODE =  :PARAMETER_CODE

Answer 2

…或使用MODEL子句

WITH    my_days AS (Select 1 as day_1, 'xx' as col_x, 'yy' as col_y From DUAL),
cte AS(SELECT INDX, day_x, col_x, col_y FROM my_days
MODEL 
DIMENSION BY(0 as INDX)
MEASURES (0 as day_x, col_x, col_y)
RULES ITERATE(31)
( day_x[ITERATION_NUMBER+1] = ITERATION_NUMBER+1,
col_x[ANY] = col_x[0],
col_y[ANY] = col_y[0]
) 
)
SELECT * FROM ct
left outer join table_1 t1 
....