在 SQL 中"WITH AS"子句不起作用。有没有其他方法？

Question

我有一个查询，像这样:

select col1,col2 from (
with RESULTSET as (
select * from t1 where rank_val=1 
)  
select  T1.col1, T1.col2    
FROM RESULTSET T1, t88a, t88b
where T1.col1=T88a.col1 and T88a.col2 = T1.col2
AND T1.col2=T88b.col2 and T88b.col1 <> T1.col1
) where NOT (c1 IS NULL AND c2 IS NULL)   ORDER BY col1, col2;

我有一个要求，我需要使用一个外部With As，如下所示:

WITH NEW AS(select col1,col2 from (
with RESULTSET as (
select * from t1 where rank_val=1 
)  
select  T1.col1, T1.col2    
FROM RESULTSET T1, t88a, t88b
where T1.col1=T88a.col1 and T88a.col2 = T1.col2
AND T1.col2=T88b.col2 and T88b.col1 <> T1.col1
) where NOT (c1 IS NULL AND c2 IS NULL)   ORDER BY col1, col2)
SELECT * FROM NEW;

它给了我一个例外:

ORA-32034: unsupported use of WITH clause
32034. 00000 -  "unsupported use of WITH clause"

如何通过删除内部的With As来重写查询。

Answer 1

将所有子查询移出到CTE中。像这样:

WITH
resultset
AS
(SELECT *
FROM t1
WHERE rank_val = 1),
temp
AS
(SELECT t1.col1, t1.col2
FROM resultset t1
JOIN t88a ON t88a.col1 = t1.col1
JOIN t88b
ON     t88b.col2 = t1.col2
AND t88b.col1 <> t1.col1),
new
AS
(SELECT col1, col2
FROM temp
WHERE NOT (    col1 IS NULL
AND col2 IS NULL))
SELECT *
FROM new
ORDER BY col1, col2;

Answer 2

您的查询可以重写为:

with RESULTSET as (
select * from t1 where rank_val=1 
),
NEW AS(
select col1,col2
from (
select  T1.col1, T1.col2    
FROM    RESULTSET T1,
t88a,
t88b
where   T1.col1=T88a.col1
and     T88a.col2 = T1.col2
AND     T1.col2=T88b.col2
and     T88b.col1 <> T1.col1
)
where  NOT (col1 IS NULL AND col2 IS NULL)
ORDER BY col1, col2
)
SELECT *
FROM   NEW;

但是，您可以将其简化为:

SELECT T1.col1, T1.col2    
FROM   T1
INNER JOIN t88a
ON (   T1.col1=T88a.col1
AND T88a.col2 = T1.col2 )
INNER JOIN t88b
ON (   T1.col2=T88b.col2
AND T88b.col1 <> T1.col1 )
WHERE  t1.rank_val=1 
AND    (  t1.col1 IS NOT NULL
OR t1.col2 IS NOT NULL )
ORDER BY col1, col2

但是WHERE子句中的NOT NULL检查是多余的，因为JOIN条件T1.col1=T88a.col1和T88a.col2 = T1.col2(等)只有在条件两侧的值为NOT NULL时才为真，因此可以进一步简化为:

SELECT T1.col1, T1.col2    
FROM   T1
INNER JOIN t88a
ON (   T1.col1=T88a.col1
AND T88a.col2 = T1.col2 )
INNER JOIN t88b
ON (   T1.col2=T88b.col2
AND T88b.col1 <> T1.col1 )
WHERE  t1.rank_val=1 
ORDER BY col1, col2

Answer 3

可以将WITH定义为单独的部分或子查询的一部分。示例:

WITH
tbl_1 AS    -- CTE 1
(
Select 'A' "LTTR", 1 "NMBR" From Dual Union ALL
Select 'B' "LTTR", 2 "NMBR" From Dual Union ALL
Select 'C' "LTTR", 3 "NMBR" From Dual 
),
tbl_2 AS    -- CTE 2
(
Select 'A' "LTTR", 10 "NMBR" From Dual Union ALL
Select 'B' "LTTR", 20 "NMBR" From Dual Union ALL
Select 'C' "LTTR", 30 "NMBR" From Dual 
)
Select
t1.LTTR "LTTR", (t1.NMBR * t2.NMBR) - x.NMBR "CALCULATION"
From
tbl_1 t1
Inner Join
tbl_2 t2 ON(t2.LTTR = t1.LTTR)
Inner Join
(WITH
tbl_x AS    -- CTE 3
(
Select 'A' "LTTR",  5 "NMBR" From Dual Union ALL
Select 'B' "LTTR", 10 "NMBR" From Dual Union ALL
Select 'C' "LTTR", 15 "NMBR" From Dual 
)
Select * From tbl_x
) x ON(x.LTTR = t1.LTTR)
Order By 
t1.LTTR

/*  R e s u l t :
LTTR CALCULATION
---- -----------
A              5 
B             30 
C             75
*/

最好将它们放在一起-以后更容易管理它们....问候…