今天看了一个这玩意,模仿着用了下,结果许多字段的值都为空,如果把(+)都去掉的话,竟然一条数据也查询不出来了只知道这东西类似于外连接,其他的就不知道,在网上也没找大相关的学习资料哪位给介绍点呀?然后帮忙看看这个,查不出数据的sql,是什么问题?select a.class_id,
a.serial_num,
a.prod_id,
bl.created,
bl.src_inv_id,
val.name valname,
bl.dest_inv_id,
bl.src_stat_id,
bl.dest_stat_id,
ws.prevfinance_id,
ws.finance_id,
bl.crm_login,
bl.created_by,
staff.staff_name,
bl.worksheet_id,
ws.session_num,
ws.reason_cd,
cs1.status_cd as SRC_STATUS_CD,
cs2.status_cd as DESC_STATUS_CD,
inv1.name as SRC_INV_NAME,
inv2.name as DEST_INV_NAME,
inv1.mis_code as SRC_MIS_CODE,
inv2.mis_code as DEST_MIS_CODE,
p.prod_name,
fs1.finance_cd as SRC_FINANCE_CD,
fs2.finance_cd as DEST_FINANCE_CD
from rm_asset a,
rm_bucket_log bl,
rm_worksheet ws,
esop.sys_staff staff,
rm_lst_of_val val,
rm_class_stat cs1,
rm_class_stat cs2,
rm_inventory inv1,
rm_inventory inv2,
rm_product p,
rm_finance_stat fs1,
rm_finance_stat fs2
where a.row_id = bl.asset_id
and bl.bucket_log_cd = val.code
and fs1.row_id(+) = ws.finance_id
and fs2.row_id(+) = ws.prevfinance_id
and cs1.row_id(+) = bl.src_stat_id
and cs2.row_id(+) = bl.dest_stat_id
and inv1.row_id(+) = bl.src_inv_id
and inv2.row_id(+) = bl.dest_inv_id
and staff.staff_id(+) = bl.created_by
and ws.row_id(+) = bl.worksheet_id
and p.row_id(+) = a.prod_id
大侠 给指导下,到底什么原因,该怎么写呢?
a.serial_num,
a.prod_id,
bl.created,
bl.src_inv_id,
val.name valname,
bl.dest_inv_id,
bl.src_stat_id,
bl.dest_stat_id,
ws.prevfinance_id,
ws.finance_id,
bl.crm_login,
bl.created_by,
staff.staff_name,
bl.worksheet_id,
ws.session_num,
ws.reason_cd,
cs1.status_cd as SRC_STATUS_CD,
cs2.status_cd as DESC_STATUS_CD,
inv1.name as SRC_INV_NAME,
inv2.name as DEST_INV_NAME,
inv1.mis_code as SRC_MIS_CODE,
inv2.mis_code as DEST_MIS_CODE,
p.prod_name,
fs1.finance_cd as SRC_FINANCE_CD,
fs2.finance_cd as DEST_FINANCE_CD
from rm_asset a,
rm_bucket_log bl,
rm_worksheet ws,
esop.sys_staff staff,
rm_lst_of_val val,
rm_class_stat cs1,
rm_class_stat cs2,
rm_inventory inv1,
rm_inventory inv2,
rm_product p,
rm_finance_stat fs1,
rm_finance_stat fs2
where a.row_id = bl.asset_id
and bl.bucket_log_cd = val.code
and fs1.row_id(+) = ws.finance_id
and fs2.row_id(+) = ws.prevfinance_id
and cs1.row_id(+) = bl.src_stat_id
and cs2.row_id(+) = bl.dest_stat_id
and inv1.row_id(+) = bl.src_inv_id
and inv2.row_id(+) = bl.dest_inv_id
and staff.staff_id(+) = bl.created_by
and ws.row_id(+) = bl.worksheet_id
and p.row_id(+) = a.prod_id
大侠 给指导下,到底什么原因,该怎么写呢?
解决方案 »
- 在orcale 11里新加的表,用外部程序或者enterprise manager,查询不到表的内容
- 表空间大小不能限制吗?
- 急!高手帮忙写个存储过程!
- 寻求解决方案:不能连接的两Oracle数据库的增量导出导入问题
- 数据库启动有问题。请各位帮帮忙啊!
- 各位大侠,请支持一把!!!!!!!!!!!!!!!!!!!!!!!!!!
- 用SQL语句批量编译函数
- oracle存储过程中循环如何实现跟C++中的continue一样的功能。
- 如何进行这样的恢复操作,提供手段即可
- 三个表的更改sql怎么写啊
- (+)这玩意怎么用啊?表示好像刚学oracle似地,之前算是白混了
- 怎样查出两条信息,一条id是固定的,另一条id要根据登陆的用户判断
select a.* from a.id(+)=b.id表示b表的有的数据都要显示,就是右关联,反之就是左关联了
--右连接,以右边的表为基准
SQL> select d.deptno,count(e.ename) cnt
2 from dept d,emp e
3 where e.deptno(+)=d.deptno
4 group by d.deptno
5 /
DEPTNO CNT
------ ----------
10 3
20 5
30 6
40 0
SQL> --等值连接
SQL> select d.deptno,count(e.ename) cnt
2 from dept d,emp e
3 where e.deptno=d.deptno
4 group by d.deptno
5 /
DEPTNO CNT
------ ----------
10 3
20 5
30 6
SQL> --左连接
SQL> select d.deptno,count(e.ename) cnt
2 from dept d,emp e
3 where e.deptno=d.deptno(+)
4 group by d.deptno
5 /
DEPTNO CNT
------ ----------
10 3
20 5
30 6
Oracle Join 图解
92标准 用(left/right) outer jion