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;执行过程如下: 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;
ls大神 我执行了您的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
为什么not exists 那一句关联的是aaa表,而不是t表,能明白我关心啥吗
你怎么能执行出来还有ID=2,NAME=李四这个记录呢?
你怎么能执行出来还有ID=2,NAME=李四这个记录呢? 事实就是如此
太神奇了,我这里执行是没有的,你自己再检查检查吧或者发个图片出来看看你的执行结果包括SQL语句
解决了,就是由于版本不同,导致执行计划不同,从而结果不同。 这是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
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 个人观点:oracle9i:红色部分语句在 两个where之后执行,oracle10g:红色部分语句 在2个where之前执行,导致结果不一致。
我当初就说过,9i的exists和10g的执行顺序不一样,就在今天,又出现了此类问题; 下面的sql在9i下没问题,但是在10g以上版本执行报错。 update A set... where exitsts(select b.* from b where ...group by b.colum1,b.colum2);
而后者是完整独立的执行两个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);