maoj1
ID NAME
1 AAA
2 BBB
3 CCC
4 DDDmaoj2
ID MONEY MONEY2
1 100 200
2 150 350
3 150
我的查询要求是查出指定数字在(moeny,money2)之间,或者指定数字大于money且money2为空
SELECT a.id,b.*
FROM maoj1 a, maoj2 b
WHERE a.ID = b.ID(+)
AND b.money (+) < 200
AND b.money2 (+) > 200;结果:
1 1 空 空 空
2 2 2 150 350
3 3 空 空 空
4 4 空 空 空
这样查出来没有ID=3的记录,SELECT a.id,b.*
FROM maoj1 a, maoj2 b
WHERE a.ID = b.ID(+)
AND b.money (+) < 200
AND (b.money2 (+) > 200 or b.money2 is null);
提示语法错误SELECT a.id,b.*
FROM maoj1 a, maoj2 b
WHERE a.ID = b.ID(+)
AND b.money (+) < 200
AND nvl(b.money2,9999) (+) > 200;
提示语法错误这问题困扰我几个星期了,再搞不定人都疯了.请大虾们帮忙,分不够再加!!!!附:建表脚本的数据脚本CREATE table MAOJ1
(
ID NUMBER(3),
NAME VARCHAR2(20)
)
;CREATE table MAOJ2
(
ID NUMBER(3),
MONEY NUMBER(4),
MONEY2 NUMBER(4)
)
;insert into MAOJ1 (ID, NAME)
values (1, 'AAA');
insert into MAOJ1 (ID, NAME)
values (2, 'BBB');
insert into MAOJ1 (ID, NAME)
values (3, 'CCC');
insert into MAOJ1 (ID, NAME)
values (4, 'DDD');
commit;prompt Loading MAOJ2...
insert into MAOJ2 (ID, MONEY, MONEY2)
values (1, 100, 200);
insert into MAOJ2 (ID, MONEY, MONEY2)
values (2, 150, 350);
insert into MAOJ2 (ID, MONEY, MONEY2)
values (3, 150, null);
commit;
ID NAME
1 AAA
2 BBB
3 CCC
4 DDDmaoj2
ID MONEY MONEY2
1 100 200
2 150 350
3 150
我的查询要求是查出指定数字在(moeny,money2)之间,或者指定数字大于money且money2为空
SELECT a.id,b.*
FROM maoj1 a, maoj2 b
WHERE a.ID = b.ID(+)
AND b.money (+) < 200
AND b.money2 (+) > 200;结果:
1 1 空 空 空
2 2 2 150 350
3 3 空 空 空
4 4 空 空 空
这样查出来没有ID=3的记录,SELECT a.id,b.*
FROM maoj1 a, maoj2 b
WHERE a.ID = b.ID(+)
AND b.money (+) < 200
AND (b.money2 (+) > 200 or b.money2 is null);
提示语法错误SELECT a.id,b.*
FROM maoj1 a, maoj2 b
WHERE a.ID = b.ID(+)
AND b.money (+) < 200
AND nvl(b.money2,9999) (+) > 200;
提示语法错误这问题困扰我几个星期了,再搞不定人都疯了.请大虾们帮忙,分不够再加!!!!附:建表脚本的数据脚本CREATE table MAOJ1
(
ID NUMBER(3),
NAME VARCHAR2(20)
)
;CREATE table MAOJ2
(
ID NUMBER(3),
MONEY NUMBER(4),
MONEY2 NUMBER(4)
)
;insert into MAOJ1 (ID, NAME)
values (1, 'AAA');
insert into MAOJ1 (ID, NAME)
values (2, 'BBB');
insert into MAOJ1 (ID, NAME)
values (3, 'CCC');
insert into MAOJ1 (ID, NAME)
values (4, 'DDD');
commit;prompt Loading MAOJ2...
insert into MAOJ2 (ID, MONEY, MONEY2)
values (1, 100, 200);
insert into MAOJ2 (ID, MONEY, MONEY2)
values (2, 150, 350);
insert into MAOJ2 (ID, MONEY, MONEY2)
values (3, 150, null);
commit;
(
select a.id, a.name , b.money, b.money2 from maoj1 a, maoj2 b where a.id = b.id(+)
)
where (&x > money and &x < money2) or (money2 is null and &x > money)
select id, name, case when ((&x > money and &x < money2) or (money2 is null and &x > money)) then money else null end, case when ((&x > money and &x < money2) or (money2 is null and &x > money)) then money2 else null end
from
(
select a.id, a.name , b.money, b.money2 from maoj1 a, maoj2 b where a.id = b.id(+)
)
FROM maoj1 a, maoj2 b
WHERE a.ID = b.ID
AND b.money < 200
AND nvl(b.money2,9999999) > 200;
FROM maoj1 a
,( select id,money,decode(money2,null,x+1,money2) money2 from maoj2 t) b
WHERE a.ID = b.ID(+)
AND b.money (+) < x
AND b.money2(+) > x
order by a.idx 的地方换成你的条件
2 from
3 (
4 select a.id, a.name , b.money, b.money2 from maoj1 a, maoj2 b where a.id = b.id(+)
5 )
6 ; ID NAME CASEWHEN((200>MONEYAND200<MONE CASEWHEN((200>MONEYAND200<MONE
---- -------------------- ------------------------------ ------------------------------
1 AAA
2 BBB 150 350
3 CCC 150
4 DDD 或者这样?
SQL> select * from
2 (
3 select a.id, a.name , b.money, b.money2 from maoj1 a, maoj2 b where a.id = b.id(+)
4 )
5 where (&x > money and &x < money2) or (money2 is null and &x > money)
6 ; ID NAME MONEY MONEY2
---- -------------------- ----- ------
2 BBB 150 350
3 CCC 150
FROM maoj1 a, maoj2 b
WHERE a.ID = b.ID(+)
AND b.money < 200
AND nvl(b.money2,9999999) > 200;
SELECT a.id,b.*
FROM maoj1 a, maoj2 b
WHERE a.ID = b.ID(+)
AND nvl(b.money ,0) < 200
AND nvl(b.money2,9999999) > 200;
就是第一种效果.maoj2不满足条件应该能外连接maoj1输出空值
就是第一种效果.maoj2不满足条件应该能外连接maoj1,其自身输出空值