发现的不正常情况
================================
用下面的SQL语句去查询下面的三张表,查询结果明显不正确!
SQL语句
================================
SELECT a.fset,b.fset, a.fkm,b.fzqdm, a.fbz,b.fbz FROM t1 a
LEFT JOIN
(SELECT b.fcode ,b.fset, b.fbz, c.fzqdm
FROM ( select fcode,fset,fbz from t2
where fdate = TO_DATE ('2005-12-31', 'yyyy-MM-dd') and
DECODE (3, 0, '', SUBSTR ((fset), - (3))) = '001') b
JOIN t3 c ON b.fcode =c.fcode
) b
ON a.fbz =b.fbz
AND DECODE(6,0, '', SUBSTR(TRIM(a.fkm),- (6)))= fzqdm
AND a.fset = DECODE(3,0, '',SUBSTR((b.fset),- (3)))表t1
===============================
create table t1 ( FKM varchar2(20), FSET varchar2(20) , FBZ char(1) )
insert into t1 values ('1101020101000009908','001','S')
insert into t1 values ('1402010101000009908','001','L')
insert into t1 values ('1402010502001009908','001','L')表t2
===============================
create table t2 (Fdate date, Fcode varchar2(20), Fset varchar2(20), Fbz char(1))
insert into t2 values ( to_date('2005-11-30','yyyy-mm-dd'),'009908','000001','L')
insert into t2 values ( to_date('2005-12-31','yyyy-mm-dd'),'009908','000001','L') 表t3
===============================
create table t3 (Fcode varchar2(20),FZqDm varchar2(20))
insert into t3 values ('009908','009908')给分标准
===============================
有建设性的意见,和真诚想讨论技术的。
================================
用下面的SQL语句去查询下面的三张表,查询结果明显不正确!
SQL语句
================================
SELECT a.fset,b.fset, a.fkm,b.fzqdm, a.fbz,b.fbz FROM t1 a
LEFT JOIN
(SELECT b.fcode ,b.fset, b.fbz, c.fzqdm
FROM ( select fcode,fset,fbz from t2
where fdate = TO_DATE ('2005-12-31', 'yyyy-MM-dd') and
DECODE (3, 0, '', SUBSTR ((fset), - (3))) = '001') b
JOIN t3 c ON b.fcode =c.fcode
) b
ON a.fbz =b.fbz
AND DECODE(6,0, '', SUBSTR(TRIM(a.fkm),- (6)))= fzqdm
AND a.fset = DECODE(3,0, '',SUBSTR((b.fset),- (3)))表t1
===============================
create table t1 ( FKM varchar2(20), FSET varchar2(20) , FBZ char(1) )
insert into t1 values ('1101020101000009908','001','S')
insert into t1 values ('1402010101000009908','001','L')
insert into t1 values ('1402010502001009908','001','L')表t2
===============================
create table t2 (Fdate date, Fcode varchar2(20), Fset varchar2(20), Fbz char(1))
insert into t2 values ( to_date('2005-11-30','yyyy-mm-dd'),'009908','000001','L')
insert into t2 values ( to_date('2005-12-31','yyyy-mm-dd'),'009908','000001','L') 表t3
===============================
create table t3 (Fcode varchar2(20),FZqDm varchar2(20))
insert into t3 values ('009908','009908')给分标准
===============================
有建设性的意见,和真诚想讨论技术的。
, (SELECT d.fcode ,d.fset, d.fbz, c.fzqdm
FROM ( select fcode,fset,fbz from t2
where fdate = TO_DATE ('2005-12-31', 'yyyy-MM-dd') and
DECODE (3, 0, '', SUBSTR ((fset), - (3))) = '001') d
JOIN t3 c ON d.fcode =c.fcode
) b
where a.fbz =b.fbz(+)
AND DECODE(6,0, '', SUBSTR(TRIM(a.fkm),- (6)))= b.fzqdm(+)
AND a.fset = DECODE(3,0, '',SUBSTR((b.fset(+)),- (3)))很奇怪,改成+后的左连接查询出来的结果只有三条了
lady, would you have a dinner with me tonight?
Hengyan.Li [18:31]:
Maggie, excuse my simple invite.But my please belive my real heart!
Hengyan.Li [18:33]:
Where are you? It's a common dinner ,don't afraid!
Maggie.Zhang [18:34]:
等下哦,
Hengyan.Li [18:34]:
OK1
Maggie.Zhang [18:34]:
我把手头东西弄好
Hengyan.Li [18:34]:
I am waiting for you!
Maggie.Zhang [18:42]:
hengyan
Hengyan.Li [18:42]:
what ?
Maggie.Zhang [18:43]:
let's have the dinner at about 19:10, is it ok with you ?
Hengyan.Li [18:43]:
haha! no problem!
人家Oracle都开发那么多年了 难道测试人员连这样的查询语句的bug都测试不出来?
那人家Oracle还混个P啊