這只是一個易于重現的示例。正如 gslamed 所建議的，有避免使用 apply 兩次的解決方案。但這不是我需要的。我需要使用兩次應用。
在這個例子中，我想在 vc 中取一個集合（tb）的第一個元素
WITH
s (a, b)
AS
(SELECT 1, 'ff' FROM DUAL
UNION ALL
SELECT 1, 'ee' FROM DUAL
UNION ALL
SELECT 1, 'ee' FROM DUAL
UNION ALL
SELECT 2, 'ee' FROM DUAL),
t (a, b)
AS
( SELECT s.a, COLLECT (s.b)
FROM s
GROUP BY s.a),
v (a, b, c)
AS
(SELECT t.a, t.b, tb.c
FROM t
OUTER APPLY (SELECT x.COLUMN_VALUE c
FROM TABLE (t.b) x
FETCH FIRST 1 ROW ONLY) tb)
SELECT *
FROM v;
它正在作業。
然后我在計算 vc 中有多少個元素
WITH
s (a, b)
AS
(SELECT 1, 'ff' FROM DUAL
UNION ALL
SELECT 1, 'ee' FROM DUAL
UNION ALL
SELECT 1, 'ee' FROM DUAL
UNION ALL
SELECT 2, 'ee' FROM DUAL),
t (a, b)
AS
( SELECT s.a, COLLECT (s.b)
FROM s
GROUP BY s.a),
v (a, b, c)
AS
(SELECT t.a, t.b, tb.c
FROM t
OUTER APPLY (SELECT count(*) c
FROM TABLE (t.b) x
) tb)
select * from v
現在我想擁有第一個元素以及 vc 和 vd 中有多少元素
WITH
s (a, b)
AS
(SELECT 1, TO_CLOB ('ff') FROM DUAL
UNION ALL
SELECT 1, TO_CLOB ('ee') FROM DUAL
UNION ALL
SELECT 1, TO_CLOB ('ee') FROM DUAL
UNION ALL
SELECT 2, TO_CLOB ('ee') FROM DUAL),
t (a, b)
AS
( SELECT s.a, COLLECT (s.b)
FROM s
GROUP BY s.a),
v (a, b, c)
AS
(SELECT t.a, t.b, tb.c
FROM t
OUTER APPLY (SELECT x.COLUMN_VALUE,count(*) c
FROM TABLE (t.b) x
FETCH FIRST 1 ROW ONLY) tb)
select * from v
ORA-00937: 不是單組組函式
它沒有醒來，這是正常的。我沒有使用 group-by 。
因此，我想用 v 中的第二個子選擇來計算它。
我試圖添加兩次 t 外部應用。但它不作業
WITH
s (a, b)
AS
(SELECT 1, TO_CLOB ('ff') FROM DUAL
UNION ALL
SELECT 1, TO_CLOB ('ee') FROM DUAL
UNION ALL
SELECT 1, TO_CLOB ('ee') FROM DUAL
UNION ALL
SELECT 2, TO_CLOB ('ee') FROM DUAL),
t (a, b)
AS
( SELECT s.a, COLLECT (s.b)
FROM s
GROUP BY s.a),
v (a, b, c,d)
AS
(SELECT t.a, t.b, tb.c,tb2.d
FROM t
OUTER APPLY (SELECT x.COLUMN_VALUE c
FROM TABLE (t.b) x
FETCH FIRST 1 ROW ONLY) tb)
OUTER APPLY (SELECT count(*) d
FROM TABLE (t.b) x
) tb2)
select * from v
ORA-00928: 缺少 SELECT 關鍵字
如何使用 2 次外部應用？
代碼
uj5u.com熱心網友回復：
你只有一個額外的右括號；當你有這個時：
AS
(SELECT t.a, t.b, tb.c,tb2.d
FROM t
OUTER APPLY (SELECT x.COLUMN_VALUE c
FROM TABLE (t.b) x
FETCH FIRST 1 ROW ONLY) tb)
--------------------------------------------------------^
... )I 標記的with子句結束。但是當你把它改成這樣時：
AS
(SELECT t.a, t.b, tb.c,tb2.d
FROM t
OUTER APPLY (SELECT x.COLUMN_VALUE c
FROM TABLE (t.b) x
FETCH FIRST 1 ROW ONLY) tb)
--------------------------------------------------------^
OUTER APPLY (SELECT count(*) d
FROM TABLE (t.b) x
) tb2)
-----------------------------------^
..您保留了結束 CTE 的右括號，因此第二個outer apply不在有效位置。只需洗掉第一個標記)并保留第二個標記)。
db<>小提琴
對于復雜的查詢，我傾向于縮進括號以使這種更明顯 - 所以第一個看起來不錯：
AS
(
SELECT t.a, t.b, tb.c,tb2.d
FROM t
OUTER APPLY (
SELECT x.COLUMN_VALUE c
FROM TABLE (t.b) x
FETCH FIRST 1 ROW ONLY
) tb
)
但第二個沒有：
AS
(
SELECT t.a, t.b, tb.c,tb2.d
FROM t
OUTER APPLY (
SELECT x.COLUMN_VALUE c
FROM TABLE (t.b) x
FETCH FIRST 1 ROW ONLY
) tb
)
OUTER APPLY (
SELECT count(*) d
FROM TABLE (t.b) x
) tb2
)
更明顯的是應該是：
AS
(
SELECT t.a, t.b, tb.c,tb2.d
FROM t
OUTER APPLY (
SELECT x.COLUMN_VALUE c
FROM TABLE (t.b) x
FETCH FIRST 1 ROW ONLY
) tb
OUTER APPLY (
SELECT count(*) d
FROM TABLE (t.b) x
) tb2
)
uj5u.com熱心網友回復：
用這個：
WITH
s (a, b)
AS
(SELECT 1, TO_CLOB ('ff') FROM DUAL
UNION ALL
SELECT 1, TO_CLOB ('ee') FROM DUAL
UNION ALL
SELECT 1, TO_CLOB ('ee') FROM DUAL
UNION ALL
SELECT 2, TO_CLOB ('ee') FROM DUAL),
t (a, b)
AS
( SELECT s.a, COLLECT (s.b)
FROM s
GROUP BY s.a),
v (a, b, c)
AS
(SELECT t.a, t.b, tb.c
FROM t
OUTER APPLY (SELECT x.COLUMN_VALUE,count(*) over() c
FROM TABLE (t.b) x
FETCH FIRST 1 ROW ONLY) tb)
select * from v
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/466322.html