如果少于配置的参数，则Oracle SQL赋值

Question

我有以下两个表：

指定

ABRANK<1>空空

VK	PC
VK1	PC1	A1	空
VK2	PC1	A1	A2	2
VK3	PC1	A2	3
VK4	PC1	A2	4
VK5	PC1	空	A1	5
VK6	PC1	空	A2	6

您的逻辑看起来是这样的：

WITH res_rows ( pc, a, maxi, total ) AS (
SELECT pc, a, maxi, SUM( maxi ) OVER( PARTITION BY pc )
FROM   res
WHERE  maxi > 0
UNION ALL
SELECT pc, a, maxi - 1, total FROM res_rows WHERE maxi > 1
),
p1 ( vk, pc, a, b, r, c, rn, pc_r ) AS (
SELECT a.*,
COALESCE(b, a),
ROW_NUMBER() OVER (PARTITION BY pc, COALESCE(b, a) ORDER BY r),
ROW_NUMBER() OVER (PARTITION BY pc ORDER BY r)
FROM   assigned a
),
p2 ( vk, pc, a, b, r, c, rn ) AS (
SELECT p1.vk,
p1.pc,
p1.a,
p1.b,
p1.r,
r.a,
CASE
WHEN r.a IS NULL
THEN ROW_NUMBER() OVER (
PARTITION BY p1.pc
ORDER BY CASE WHEN r.a IS NULL THEN p1.r END
)
ELSE p1.rn
END
FROM   p1
LEFT OUTER JOIN res_rows r
ON ( p1.pc = r.pc AND p1.c = r.a AND p1.rn = r.maxi AND p1.pc_r <= total )
),
missing ( pc, a, rn ) AS (
SELECT pc,
a,
ROW_NUMBER() OVER ( PARTITION BY pc ORDER BY ROWNUM )
FROM   (
SELECT pc, a, maxi FROM res_rows
MINUS
SELECT pc, c, rn FROM p2 WHERE c IS NOT NULL
)
)
SELECT p2.vk,
p2.pc,
p2.a,
p2.b,
p2.r,
COALESCE( m.a, p2.c ) AS c
FROM   p2
LEFT OUTER JOIN missing m
ON ( p2.pc = m.pc AND p2.c IS NULL AND p2.rn = m.rn )
ORDER BY r

哪个输出：

BRC1>

VK	PC	A
VK1	PC1	A1	A1
VK2	PC1	A1	A2	2	A2
VK3	PC1	A2		3	A2
VK4	PC1	A2		4	A1
VK5	PC1			A1	5
VK6	PC1			A2	6