SELECT COUNT(p.prod_id) --结果1070877
FROM product p
JOIN party t ON t.party_id = p.owner_id
LEFT OUTER prod_2_addr a ON a.prod_id = p.prod_id
AND a.reason_cd = 1
LEFT OUTER JOIN (SELECT segment_id, party_id
FROM segment_member
WHERE segment_id IN (31, 32)) l ON l.party_id =
t.party_id
LEFT OUTER JOIN customer s ON p.prod_id = s.prod_id
WHERE s.serv_spec_id IN (467, 455, 456, 457)
AND p.prod_spec_id IN (1, 2);
--------------------------------------------------------------------------
SELECT count(p.prod_id) --结果303491
FROM product p,
party t,
prod_2_addr a,
(SELECT segment_id, party_id
FROM segment_member
WHERE segment_id IN
(31, 32)) l,
customer s
WHERE t.party_id = p.owner_id
AND a.prod_id = p.prod_id(+)
AND a.reason_cd = 1
AND l.party_id = t.party_id(+)
AND p.prod_id = s.prod_id
AND s.serv_spec_id IN (4, 5, 6, 7)
AND p.prod_spec_id IN (1, 2);
请大伙帮忙看看,请急的,为什么下面两个语句的执行结果会不一样?
第一条在9i下执行,结果为1070877, 第二条在8i下执行,结果为303491.
如果以第一条语句为标准,第二条语句哪里不对了,该如何改呢?谢谢!
FROM product p
JOIN party t ON t.party_id = p.owner_id
LEFT OUTER prod_2_addr a ON a.prod_id = p.prod_id
AND a.reason_cd = 1
LEFT OUTER JOIN (SELECT segment_id, party_id
FROM segment_member
WHERE segment_id IN (31, 32)) l ON l.party_id =
t.party_id
LEFT OUTER JOIN customer s ON p.prod_id = s.prod_id
WHERE s.serv_spec_id IN (467, 455, 456, 457)
AND p.prod_spec_id IN (1, 2);
--------------------------------------------------------------------------
SELECT count(p.prod_id) --结果303491
FROM product p,
party t,
prod_2_addr a,
(SELECT segment_id, party_id
FROM segment_member
WHERE segment_id IN
(31, 32)) l,
customer s
WHERE t.party_id = p.owner_id
AND a.prod_id = p.prod_id(+)
AND a.reason_cd = 1
AND l.party_id = t.party_id(+)
AND p.prod_id = s.prod_id
AND s.serv_spec_id IN (4, 5, 6, 7)
AND p.prod_spec_id IN (1, 2);
请大伙帮忙看看,请急的,为什么下面两个语句的执行结果会不一样?
第一条在9i下执行,结果为1070877, 第二条在8i下执行,结果为303491.
如果以第一条语句为标准,第二条语句哪里不对了,该如何改呢?谢谢!
解决方案 »
- 求推Oracle经典书籍!!!!!!!
- oracle nvarchar2 右边补空
- 再问个这种存储过程当有记录返回记录没记录就返回-1,一定要存储过程的,谢谢
- LDAP 和 怎么和orcale联系起来
- 在英文操作系统中安装pl/sql后,存储过程的中文注释,全部是乱码,求达人相助!
- 我的ORACLE是默认安装的,请问要怎样配置才能让一台没有装ORACLE的机子使用我机子上的ORACLE数据库呀??
- 可以在存储过程(pl/sql)中 mv 磁盘上的一个文件吗?如果可以,怎么写?
- 请问在oracle中用一个表的id为a记录更新id为b记录,怎么写sql语句?
- 高手请来!!long型字段实现模糊搜索问题!
- 最后一位数向前添加0如何处理
- 关于使用第三方工具 SQL Monitor 监测VB6 调用问题
- java.sql.SQLException: 用尽的 Resultset 如何解决
SELECT COUNT(p.prod_id)
FROM FROM product p,
party t,
(SELECT prod_id
FROM prod_2_addr
WHERE reason_cd = 1) a
WHERE t.party_id = p.owner_id
AND (a.prod_id = p.prod_id(+));--此时的结果为4232796
而语句:
SELECT COUNT(p.prod_id)
FROM product p
JOIN party t ON t.party_id = p.owner_id
LEFT OUTER prod_2_addr a ON a.prod_id = p.prod_id
AND a.reason_cd = 1--执行的结果是6449133
问题就从这里开始了,望有高手帮忙分析分析,不胜感谢!