今天看了一个这玩意,模仿着用了下,结果许多字段的值都为空,如果把(+)都去掉的话,竟然一条数据也查询不出来了只知道这东西类似于外连接,其他的就不知道,在网上也没找大相关的学习资料哪位给介绍点呀?然后帮忙看看这个,查不出数据的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
大侠 给指导下,到底什么原因,该怎么写呢?
解决方案 »
- 请教SQL
- oracle中存储过程里for循环如何每一次循环都从表中取出相邻的两条记录
- 哪位大哥帮下忙,写个存储过程,取temp表中所有的记录!!!谢谢了!!!
- 关于Oracle这个存储过程,研究了两天,都不知道怎么下手,大家看看!实在是急啊
- OMWB迁移问题,都几天啦还没有好快呀~~!
- 想学oracle,该用什么版本的进行学习呢?
- oracle行转列~~~~~~~~~~~~
- oracle 怎么实现bulk insert 方法?
- 各位同道:oracle9i用遊標返回記錄集,在 java/jsp 中怎樣接收?!!謝謝!!
- 本机A怎么访问B机的数据库
- 试图创建 更改或删除正在使用的临时表中的索引,谁帮我看看这个错误
- (+)这玩意怎么用啊?表示好像刚学oracle似地,之前算是白混了
在右连是左连接看看这个就明白了在9i以前可以这么写:
左联:
select a.id,a.name,b.address from a,b
where a.id=b.id(+)
右联:
select a.id,a.name,b.address from a,b
where a.id(+)=b.id
外联
SELECT a.id,a.name,b.address
FROM a,b
WHERE a.id = b.id(+)
UNION
SELECT b.id,'' name,b.address
FROM b
WHERE NOT EXISTS (
SELECT * FROM a
WHERE a.id = b.id);在9i以上,已经开始支持SQL99标准,所以,以上语句可以写成:
默认内部联结:
select a.id,a.name,b.address,c.subject
from (a inner join b on a.id=b.id)
inner join c on b.name = c.name
where other_clause
左联
select a.id,a.name,b.address
from a left outer join b on a.id=b.id
where other_clause
右联
select a.id,a.name,b.address
from a right outer join b on a.id=b.id
where other_clause
外联
select a.id,a.name,b.address
from a full outer join b on a.id=b.id
where other_clause
or
select a.id,a.name,b.address
from a full outer join b using (id)
where other_clause
在右连是左连接看看这个就明白了在9i以前可以这么写:
左联:
select a.id,a.name,b.address from a,b
where a.id=b.id(+)
右联:
select a.id,a.name,b.address from a,b
where a.id(+)=b.id
外联
SELECT a.id,a.name,b.address
FROM a,b
WHERE a.id = b.id(+)
UNION
SELECT b.id,'' name,b.address
FROM b
WHERE NOT EXISTS (
SELECT * FROM a
WHERE a.id = b.id);在9i以上,已经开始支持SQL99标准,所以,以上语句可以写成:
默认内部联结:
select a.id,a.name,b.address,c.subject
from (a inner join b on a.id=b.id)
inner join c on b.name = c.name
where other_clause
左联
select a.id,a.name,b.address
from a left outer join b on a.id=b.id
where other_clause
右联
select a.id,a.name,b.address
from a right outer join b on a.id=b.id
where other_clause
外联
select a.id,a.name,b.address
from a full outer join b on a.id=b.id
where other_clause
or
select a.id,a.name,b.address
from a full outer join b using (id)
where other_clause
在右连是左连接看看这个就明白了在9i以前可以这么写:
左联:
select a.id,a.name,b.address from a,b
where a.id=b.id(+)
右联:
select a.id,a.name,b.address from a,b
where a.id(+)=b.id
外联
SELECT a.id,a.name,b.address
FROM a,b
WHERE a.id = b.id(+)
UNION
SELECT b.id,'' name,b.address
FROM b
WHERE NOT EXISTS (
SELECT * FROM a
WHERE a.id = b.id);在9i以上,已经开始支持SQL99标准,所以,以上语句可以写成:
默认内部联结:
select a.id,a.name,b.address,c.subject
from (a inner join b on a.id=b.id)
inner join c on b.name = c.name
where other_clause
左联
select a.id,a.name,b.address
from a left outer join b on a.id=b.id
where other_clause
右联
select a.id,a.name,b.address
from a right outer join b on a.id=b.id
where other_clause
外联
select a.id,a.name,b.address
from a full outer join b on a.id=b.id
where other_clause
or
select a.id,a.name,b.address
from a full outer join b using (id)
where other_clause
恩,左联右连这个懂了,但还是有点晕,为什么我的那个sql那么好几个字段都是空呢?
以 a.id(+)=b.aid 这个右连接为例,现在我们称b表为主表,a表为次表;
如果存在where中的条件同时又有这样一个 c.id(+)=a.cid;那么现在,a成了主表,c为次表。也就是说,一个表如果同时即作主表,也做次表,那么就会导致这个表里的某些数据查不到
虽然找到了这个问题,但是我还是不知道该怎么解决哪位大侠给指点下
然后把其它的表关联查询当作一个表处理,然后用ws表和这个“表”外关联
例如:
select
字段1,字段2,字段...
from
(select
t1.row_id as t1_row_id,
......
......
from t1,t2
where ......) th,rm_worksheet ws
where ws.row_id(+) = th.t1_row_id