Oracle 12c多WITH AS问题：如何让FixedSet值在DataSet中恒定

嘿，我明白你的痛点了——原来的交叉连接会让val1、val2每行都重新生成，所以导致它们没法保持恒定。核心问题是要把固定随机值的生成和动态行数的生成拆分开，让val1、val2只生成一次，然后所有行都复用这两个值。

下面是修改后的完整SQL语句，我会一步步给你拆解：

WITH fixed_values AS (
    -- 只生成一次1-30之间的固定随机值val1、val2
    SELECT 
        FLOOR(DBMS_RANDOM.VALUE(1, 31)) AS val1,
        FLOOR(DBMS_RANDOM.VALUE(1, 31)) AS val2
    FROM DUAL
),
dynamic_rows AS (
    -- 生成4-15行的动态数据集，包含Ident和随机val
    SELECT 
        LEVEL AS Ident,
        -- 生成正负随机数，这里用-100到100范围，你可以按需调整
        DBMS_RANDOM.VALUE(-100, 100) AS val
    FROM DUAL
    CONNECT BY LEVEL <= FLOOR(DBMS_RANDOM.VALUE(4, 16))
)
-- 将固定值和动态行关联，确保所有行共用同一组val1、val2
SELECT 
    dr.Ident,
    dr.val,
    fv.val1,
    fv.val2,
    -- 根据val的正负返回对应固定值
    CASE 
        WHEN dr.val < 0 THEN fv.val1
        ELSE fv.val2
    END AS result_val
FROM dynamic_rows dr
CROSS JOIN fixed_values fv;

关键细节解释： #

1. fixed_values子句：

   • 用FROM DUAL确保只生成一行数据，这样val1和val2只会被计算一次，全程保持固定。
   • FLOOR(DBMS_RANDOM.VALUE(1,31))：因为DBMS_RANDOM.VALUE(a,b)生成的是[a,b)区间的数，所以用31作为上限才能包含30，FLOOR用来取整得到整数。

2. dynamic_rows子句：

   • CONNECT BY LEVEL <= FLOOR(DBMS_RANDOM.VALUE(4,16))：同样利用区间特性，生成4到15之间的随机行数（因为16是上限，FLOOR后最大是15）。
   • LEVEL直接作为Ident，简单直观；val用DBMS_RANDOM.VALUE(-100,100)生成正负都有的随机数，你可以根据需求调整范围。

3. 关联与结果计算：

   • 用CROSS JOIN关联两个子句，因为fixed_values只有一行，所以不会产生多余的行，所有动态行都会复用同一组val1、val2。
   • CASE语句实现了“val为负返回val1，为正返回val2”的逻辑，如果你需要处理val=0的情况，可以单独加一个分支（比如WHEN dr.val = 0 THEN ...）。

验证效果： #

每次执行这个语句，你会看到：

• 行数在4到15之间随机变化
• 所有行的val1和val2完全相同
• result_val会根据每行val的正负自动切换val1或val2

内容的提问来源于stack exchange，提问作者BartmanDilaw