,先过滤 not exists 后 分组
解决方案 »
- OraOps10.dll相关问题,求高手解答
- 请问,如何用PL/SQL在两台机器上进行文件的复制?
- 一个简单查询
- 菜鸟问题 : 怎样启动 oracle9i 服务
- 关于oracle 11i 的安装问题
- trigger或package停止运行可能会有哪些原因(经验丰富的高手/准高手请进)
- 请教个简单问题。我怎么用Sql命令得到我建立的存储过程的名字,又怎么参看某个存储过程呢?谢谢
- 请问一个简单sql语句的执行原理
- 求助,oracle数据库的联合查询问题。
- oracle中序列每隔一天就重新循环 怎么弄? 搜索
- Oracle创建一个表 如果不指定表空间 是不是就是用默认的system表空间
- 使用PL/SQL创建数据表时出现缺失右括号和标识符无效的错误,怎么解决?
而后者是完整独立的执行两个SQL,然后再做减法。
而后者是完整独立的执行两个SQL,然后再做减法。
from (select max(id) id, name from aaa group by name) t
where not exists (select 1
from (select max(id) id, name from b group by name) b
where b.id = t.id
and b.name = t.name)
order by t.id;执行过程如下:
1、首先执行select max(id) id, name from aaa group by name,结果如下:
id name
4 张三
5 李四
3 王五
6 xxx
2、然后执行select max(id) id, name from b group by name,结果如下:
id name
1 张三
5 李四
4 王五
3、然后在1的结果里面对1中的每一行的ID和1的NAME,在2的结果中逐行扫描不满足条件(1.id=2.id and 1.name=2.name)的记录并根据ID升序输出,结果如下:
id name
3 王五
4 张三
6 xxx
--所以LZ的第一个SQL结果中居然有ID=2,NAME=李四,这样的输出让人费解?select max(id) id, name from aaa group by name
minus
select max(id) id, name from b group by name;执行过程如下:
1、首先执行select max(id) id, name from aaa group by name,结果如下:
id name
4 张三
5 李四
3 王五
6 xxx
2、然后执行select max(id) id, name from b group by name,结果如下:
id name
1 张三
5 李四
4 王五
3、执行MINUS操作符,将1的结果减去2的结果,相当于在1的结果里面根据1中的每一行的ID和1的NAME,在2的结果中逐行扫描不满足条件(1.id=2.id and 1.name=2.name)的记录并输出,结果如下:
id name
3 王五
4 张三
6 xxx所以两者结果应该一致。LZ有没有在PLSQL中尝试过后再来问问题呢?SQL如下:WITH aaa AS(
SELECT '1' ID, '张三' name FROM dual
UNION ALL
SELECT '2' , '李四' FROM dual
union all
SELECT '3' , '王五' FROM dual
union all
SELECT '4' , '张三' FROM dual
union all
SELECT '5' , '李四' FROM dual
union all
SELECT '6' , 'xxx' FROM dual
),
b AS(
SELECT '1' ID, '张三' name FROM dual
UNION ALL
SELECT '2' , '李四' FROM dual
union all
SELECT '3' , '王五' FROM dual
union all
SELECT '4' , '王五' FROM dual
union all
SELECT '5' , '李四' FROM dual
)
select *
from (select max(id) id, name from aaa group by name) t
where not exists (select 1
from (select max(id) id, name from b group by name) b
where b.id = t.id
and b.name = t.name)
order by t.id;WITH aaa AS(
SELECT '1' ID, '张三' name FROM dual
UNION ALL
SELECT '2' , '李四' FROM dual
union all
SELECT '3' , '王五' FROM dual
union all
SELECT '4' , '张三' FROM dual
union all
SELECT '5' , '李四' FROM dual
union all
SELECT '6' , 'xxx' FROM dual
),
b AS(
SELECT '1' ID, '张三' name FROM dual
UNION ALL
SELECT '2' , '李四' FROM dual
union all
SELECT '3' , '王五' FROM dual
union all
SELECT '4' , '王五' FROM dual
union all
SELECT '5' , '李四' FROM dual
)
select max(id) id, name from aaa group by name
minus
select max(id) id, name from b group by name;
我执行了您的sql脚本WITH aaa AS(
SELECT '1' ID, '张三' name FROM dual
UNION ALL
SELECT '2' , '李四' FROM dual
union all
SELECT '3' , '王五' FROM dual
union all
SELECT '4' , '张三' FROM dual
union all
SELECT '5' , '李四' FROM dual
union all
SELECT '6' , 'xxx' FROM dual
),
b AS(
SELECT '1' ID, '张三' name FROM dual
UNION ALL
SELECT '2' , '李四' FROM dual
union all
SELECT '3' , '王五' FROM dual
union all
SELECT '4' , '王五' FROM dual
union all
SELECT '5' , '李四' FROM dual
)
select *
from (select max(id) id, name from aaa group by name) t
where not exists (select 1
from (select max(id) id, name from b group by name) b
where b.id = t.id
and b.name = t.name)
order by t.id;WITH aaa AS(
SELECT '1' ID, '张三' name FROM dual
UNION ALL
SELECT '2' , '李四' FROM dual
union all
SELECT '3' , '王五' FROM dual
union all
SELECT '4' , '张三' FROM dual
union all
SELECT '5' , '李四' FROM dual
union all
SELECT '6' , 'xxx' FROM dual
),
b AS(
SELECT '1' ID, '张三' name FROM dual
UNION ALL
SELECT '2' , '李四' FROM dual
union all
SELECT '3' , '王五' FROM dual
union all
SELECT '4' , '王五' FROM dual
union all
SELECT '5' , '李四' FROM dual
)
select max(id) id, name from aaa group by name
minus
select max(id) id, name from b group by name;
结果:
with
id name
2 李四
3 王五
4 张三
6 xxxwith
id name
3 王五
4 张三
6 xxx
您说的这句话就是我想问的问题:“--所以LZ的第一个SQL结果中居然有ID=2,NAME=李四,这样的输出让人费解?”。
PL/SQL Developer Version 7.0.1.1066 (MBCS)
18253.6320 - Unlimited user license
Windows XP version 6.0 (build 2600) Service Pack 3oracle 9i
你怎么能执行出来还有ID=2,NAME=李四这个记录呢?
事实就是如此
这是9i的执行计划:
SELECT STATEMENT, GOAL = CHOOSE
SORT ORDER BY
SORT GROUP BY
FILTER
TABLE ACCESS FULL Object owner=EPAMSNCL Object name=AAA
FILTER
SORT GROUP BY
TABLE ACCESS FULL Object owner=EPAMSNCL Object name=B10g的执行计划:
SELECT STATEMENT, GOAL = CHOOSE Cost=24 Cardinality=1 Bytes=7
SORT ORDER BY Cost=24 Cardinality=1 Bytes=7
FILTER
VIEW Object owner=EPAMSNCL Cost=13 Cardinality=6 Bytes=42
HASH GROUP BY Cost=13 Cardinality=6 Bytes=42
VIEW Object owner=EPAMSNCL Cost=12 Cardinality=6 Bytes=42
UNION-ALL
FILTER
SORT GROUP BY NOSORT Cost=10 Cardinality=5 Bytes=45
VIEW Object owner=EPAMSNCL Cost=10 Cardinality=5 Bytes=45
UNION-ALL
from (select max(id) id, name from aaa group by name) t
where not exists (select 1
from (select max(id) id, name from b group by name) b
where b.id = t.id
and b.name = t.name)
order by t.id
个人观点:oracle9i:红色部分语句在 两个where之后执行,oracle10g:红色部分语句 在2个where之前执行,导致结果不一致。
下面的sql在9i下没问题,但是在10g以上版本执行报错。
update A set...
where exitsts(select b.* from b where ...group by b.colum1,b.colum2);