SQL - 从连接表中提取随机样本

Question

我必须用 TAB_B 中的随机记录 (5%) 填充一个空表 (TAB_A)，并与其他 3 个表（C1、C2、C3）连接。

现在，如果我运行以下查询，一切正常：

INSERT INTO TAB_A (field1,field2)
    SELECT TAB_B.ID_TASS, TAB_B.ID_SEZ
    FROM (SELECT TAB_B.*, row_number() 
            OVER (ORDER BY dbms_random.VALUE) r 
            FROM TAB_B) 
    WHERE r < (SELECT COUNT(*) FROM TAB_B)*0.05

问题是我还需要填写 TAB_A.field3，其值位于 TAB_C3 中。 所以我重写了之前的查询：

INSERT INTO TAB_A (field1,field2,field3)
    SELECT B.ID_TASS, B.ID_SEZ, C3.Z_ID
    FROM (SELECT B.*, row_number() 
            OVER (ORDER BY dbms_random.VALUE) r 
            FROM TAB_B B
                JOIN TAB_C1 C1 on C1.X_ID = B.X_ID
                JOIN TAB_C2 C2 on C2.Y_ID = C1.Y_ID
                JOIN TAB_C3 C3 on C3.Z_ID = C2.Z_ID
            WHERE C3.Z_ID = 9) 
    WHERE r < (SELECT COUNT(*) FROM B)*0.05

但是出现错误：ORA-00904:“C3.Z_ID”无效标识符。

我认为问题可能出在我在“SELECT TAB_B.ID_TASS, TAB_B.ID_SEZ, TAB_C3”中更改的语句“SELECT B.ID_TASS, B.ID_SEZ, C3.Z_ID”中。 Z_ID”....但没有成功：同样的错误再次发生。

顺便说一句，以下查询完美运行：

select count(*)
    from TAB_B B
        JOIN TAB_C1 C1 on C1.X_ID = B.X_ID
        JOIN TAB_C2 C2 on C2.Y_ID = C1.Y_ID
        JOIN TAB_C3 C3 on C3.Z_ID = C2.Z_ID
    WHERE C3.Z_ID = 9

有人可以帮助我吗？

Answer 1

尝试

...

SELECT B.ID_TASS, B.ID_SEZ, C3_Z_ID

FROM (SELECT B.*, C3.Z_ID AS C3_Z_ID, row_number()
...

Answer 2

尽管我还没有测试过，以下应该可以工作

INSERT INTO TAB_A (field1,field2,field3)
>     SELECT intTab.ID_TASS, intTab.ID_SEZ, intTab.Z_ID
>     FROM (SELECT B.*, c3.z_id,row_number() 
>             OVER (ORDER BY dbms_random.VALUE) r 
>             FROM TAB_B B
>                 JOIN TAB_C1 C1 on C1.X_ID = B.X_ID
>                 JOIN TAB_C2 C2 on C2.Y_ID = C1.Y_ID
>                 JOIN TAB_C3 C3 on C3.Z_ID = C2.Z_ID
>             WHERE C3.Z_ID = 9) intTab
>     WHERE intTab.r < (SELECT COUNT(*) FROM B)*0.05