我有Oracle 11g数据库。当我执行查询containigWITH
子句时,它不会显示任何结果。
询问:
WITH
seq AS (SELECT to_char(LEVEL) p
FROM dual CONNECT BY LEVEL <= 10)
SELECT old_value, new_value
FROM (SELECT to_char(t1.p) old_value,
to_char(t2.p) new_value
FROM (SELECT row_number() over(ORDER BY p) rn, p FROM seq) t1,
(SELECT row_number() over(ORDER BY dbms_random.random) rn,
p
FROM seq) t2
WHERE t1.rn = t2.rn);
当我执行此查询时,它不显示任何记录: 0 rows selected in 0.0078 seconds.
为什么结果为空?
它在11.2.0.2上对我有效:
with seq as (select to_char (level) p
from dual
connect by level <= 10)
select old_value,
new_value
from (select t1.p old_value,
t2.p new_value
from (select row_number () over (order by p) rn, p from seq) t1,
(select row_number () over (order by dbms_random.random) rn, p
from seq) t2
where t1.rn = t2.rn);
OLD_VALUE NEW_VALUE
---------- ----------
1 7
10 5
2 4
3 6
4 3
5 8
6 1
7 2
8 9
9 10
注意:尽管它与to_char(t1.p)
and to_char(t2.p)
/ old / newvalue列定义一起使用,但是那些to_chars完全是多余的,因为t1.p和t2.p已经是字符串(在seq子查询中定义)。这就是为什么我在上面的查询中删除了它们。
ETA:如果要使用子查询分解(这是Oracle的CTE术语),为什么不花大量精力把所有子查询放在WITH子句中呢?
例如:
with seq as (select to_char (level) p
from dual
connect by level <= 10),
t1 as (select row_number () over (order by p) rn,
p
from seq),
t2 as (select row_number () over (order by dbms_random.random) rn,
p
from seq)
select t1.p old_value,
t2.p new_value
from t1
inner join t2 on (t1.rn = t2.rn);
还有两点需要注意:a)我删除了不必要的外部查询,并且b)将旧样式的连接转换为ANSI样式的连接。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句