这样两个表
表一 his_fund:
MONEY_TYPE CURRENT_BALANCE CLIENT_ID
0 1314553.72 22123188
表二 his_asset:
MONEY_TYPE FUND_ACCOUNT TOTAL_ASSET
0 123456 4990838.72
1 123456 0.00
2 123456 0.00
我希望查询出来的结果是
MONEY_TYPE FUND_ACCOUNT TOTAL_ASSET CURRENT_BALANCE
0 123456 4990838.72 1314553.72
1 123456 0.00
2 123456 0.00
可是查询语句
我这样写
select *
from his.hisasset a , hisfund b
where a.fund_account='123456' and a.init_date='20100818' and a.init_date=b.init_date and a.money_type(+)=b.money_type and a.fund_account=b.fund_account
结果却只能出来一条money_type=0的记录,怎么也不能让1和2的空记录显示出来……求助各位大侠指点一下,我是很新的菜鸟……
表一 his_fund:
MONEY_TYPE CURRENT_BALANCE CLIENT_ID
0 1314553.72 22123188
表二 his_asset:
MONEY_TYPE FUND_ACCOUNT TOTAL_ASSET
0 123456 4990838.72
1 123456 0.00
2 123456 0.00
我希望查询出来的结果是
MONEY_TYPE FUND_ACCOUNT TOTAL_ASSET CURRENT_BALANCE
0 123456 4990838.72 1314553.72
1 123456 0.00
2 123456 0.00
可是查询语句
我这样写
select *
from his.hisasset a , hisfund b
where a.fund_account='123456' and a.init_date='20100818' and a.init_date=b.init_date and a.money_type(+)=b.money_type and a.fund_account=b.fund_account
结果却只能出来一条money_type=0的记录,怎么也不能让1和2的空记录显示出来……求助各位大侠指点一下,我是很新的菜鸟……
SELECT A.MONEY_TYPE,A.FUND_ACCOUNT,A.TOTAL_ASSET,F.CURRENT_BALANCE
FROM HIS_ASSET A LEFT JOIN HIS_FUND F
ON A.MONEY_TYPE=F.MONEY_TYPE AND A.FUND_ACCOUNT='123456' AND A.INIT_DATE='20100818';--不带过滤条件
SELECT A.MONEY_TYPE,A.FUND_ACCOUNT,A.TOTAL_ASSET,F.CURRENT_BALANCE
FROM HIS_ASSET A LEFT JOIN HIS_FUND F
ON A.MONEY_TYPE=F.MONEY_TYPE;
create table his_funds(money_type number, current_balance number, client_id number);
create table his_asset(money_type number, fund_account number, total_asset number);insert into his_funds values (0, 1314553.72, 22123188);
insert into his_asset values (0, 123456, 4990838.72);
insert into his_asset values (1, 123456, 0.00);
insert into his_asset values (2, 123456, 0.00);commit;select F.MONEY_TYPE, A.FUND_ACCOUNT, A.TOTAL_ASSET, F.CURRENT_BALANCE
from his_funds f, his_asset a
where f.money_type(+) = a.money_type
and A.FUND_ACCOUNT = 123456;MONEY_TYPE FUND_ACCOUNT TOTAL_ASSET CURRENT_BALANCE
---------- ------------ ----------- ---------------
0 123456 4990838.72 1314553.72
123456 0
123456 0 加號放錯地方了
from his_funds f, his_asset a
where f.money_type(+) = a.money_type
and A.FUND_ACCOUNT = 123456;MONEY_TYPE FUND_ACCOUNT TOTAL_ASSET CURRENT_BALANCE
---------- ------------ ----------- ---------------
0 123456 4990838.72 1314553.72
1 123456 0
2 123456 0
建议楼主如果对+号不是很理解的话,就向我一样使用left join或right join这样不容易错误。
其实我一直对+号就是使用不来,一直都使用的是left join或right join。
select *
from his.hisasset a , hisfund b
where a.fund_account(+)='123456'
and a.init_date(+)='20100818'
and a.init_date(+)=b.init_date
and a.money_type(+)=b.money_type
and a.fund_account(+)=b.fund_account
(+)是一种快被淘汰的写法。