WITH t1 AS (
SELECT t_b.ci,t_a.all_id,row_number() over(PARTITION BY t_b.ci ORDER BY t_a.all_id) rn
FROM t_a
LEFT JOIN t_b PARTITION BY (t_b.ci)
ON t_a.all_id = t_b.current_id
WHEREt_b.current_id IS NULL
),
t2 AS(
SELECT ci,comments,row_number() over(PARTITION BY ci ORDER BY comments) rn
FROM t_c
)
SELECT t2.ci,t2.comments||'_'||t1.all_id b
FROM t1,t2
WHERE t1.ci = t2.ci AND t1.rn = t2.rn
;
ci1txt1_3
ci1txt2_5
ci3txt3_1
ci4txt4_4
ci4txt5_5
|
