表t1结构如下:
plat_msg_id,target_appl_code,target_appl_message_type_code,source_appl_code,
process_code ---
t2表结构如下:
appl_code,appl_name---
t3表结构如下:
message_type_code,message_type_name---
t4表结构如下:
process_code,process_name---
现在的操作是想查询得到如下结构集
t1.plat_msg_id,t1.target_appl_name(t2表中appl_code 对应的appl_name),target_appl_message_type_name(t3表中message_type_code中对应的message_type_name),source_appl_name(t2表中appl_code 对应的appl_name),process_code(t4表中process_code对应的process_name)
plat_msg_id,target_appl_code,target_appl_message_type_code,source_appl_code,
process_code ---
t2表结构如下:
appl_code,appl_name---
t3表结构如下:
message_type_code,message_type_name---
t4表结构如下:
process_code,process_name---
现在的操作是想查询得到如下结构集
t1.plat_msg_id,t1.target_appl_name(t2表中appl_code 对应的appl_name),target_appl_message_type_name(t3表中message_type_code中对应的message_type_name),source_appl_name(t2表中appl_code 对应的appl_name),process_code(t4表中process_code对应的process_name)
from t1 a inner join t2 b
on a.target_appl_code=b.appl_code
inner join t2 c
on a.source_appl_code=c.appl_code
用这样的方式
两次连接t2
d.message_type_name as target_appl_message_type_name,
c.appl_name as source_appl_name,
e.process_name as process_code
from t1 a inner join t2 b
on a.target_appl_code=b.appl_code
inner join t3 d
on a.target_appl_message_type_code=d.message_type_code
inner join t2 c
on a.source_appl_code=c.appl_code
inner join t4 e
on a.process_code=e.process_code
刚才没有看清楚 原来的查询是带子查询的 就是还带一个分页的条件
刚才的实现是这样的
select plat_message_id as platMessageId,
source_appl_code as sourceApplCode(这个字段要引用t2.appl_name)
,target_appl_code as targetApplCode(这个字段要引用t2.appl_name),
target_message_type_code as targetMessageTypeCode, (这个字段要引用3.message_type_name)
process_plat as processPlat ...
from(select a.* , rownum r
from dexpt_data_log a
where rownum <= {endRow} and {sql: whereStr})b
where r >= {startRow}
在这种情况下该怎么处理 inner join啊。因为这上面有个分页查询的实现
不知道该怎么添加inner join----
谢谢啊
一定加分:)
a.plat_msg_id,b.appl_name as target_appl_name,
d.message_type_name as target_appl_message_type_name,
c.appl_name as source_appl_name,
e.process_name as process_code
from t1 a inner join t2 b
on a.target_appl_code=b.appl_code
inner join t3 d
on a.target_appl_message_type_code=d.message_type_code
inner join t2 c
on a.source_appl_code=c.appl_code
inner join t4 e
on a.process_code=e.process_code
from dexpt_data_log a
where rownum <= {endRow} and {sql: whereStr})b
where r >= {startRow}
a 和t1 有什么关系阿
不太会啊 oralce 10G中,怎么操作啊
--
可以阿
select *
from a inner join b
on..
inner join c
on ....
where
....
select plat_message_id as platMessageId,
source_appl_code as sourceApplCode(这个字段要引用t2.appl_name)
,target_appl_code as targetApplCode(这个字段要引用t2.appl_name),
target_message_type_code as targetMessageTypeCode, (这个字段要引用3.message_type_name)
process_plat as processPlat ...
from(select a.* , rownum r
from dexpt_data_log a
where rownum <= {endRow} and {sql: whereStr})b
where r >= {startRow}
在这种情况下该怎么处理 inner join啊。因为这上面有个分页查询的实现
不知道该怎么添加inner join----
这样可以吗?
怎么加inner join
as
select 。
把这个作为a
(select h.* , rownum r
from
(
a.plat_msg_id,b.appl_name as target_appl_name,
d.message_type_name as target_appl_message_type_name,
c.appl_name as source_appl_name,
e.process_name as process_code
from t1 a inner join t2 b
on a.target_appl_code=b.appl_code
inner join t3 d
on a.target_appl_message_type_code=d.message_type_code
inner join t2 c
on a.source_appl_code=c.appl_code
inner join t4 e
on a.process_code=e.process_code)
h
where rownum <= {endRow} and {sql: whereStr})j
where r >= {startRow}
===========================================================
create view viewname
as
select a.plat_msg_id,b.appl_name as target_appl_name,
d.message_type_name as target_appl_message_type_name,
c.appl_name as source_appl_name,
e.process_name as process_code
from t1 a inner join t2 b
on a.target_appl_code=b.appl_code
inner join t3 d
on a.target_appl_message_type_code=d.message_type_code
inner join t2 c
on a.source_appl_code=c.appl_code
inner join t4 e
on a.process_code=e.process_code
---
select *
from(select a.* , rownum r
from viewname a
where rownum <= {endRow} and {sql: whereStr})b
where r >= {startRow}
这样会少一些记录的。
如t1target_appl_code 为空的那些记录,就会少掉了
还是有问题啊,left outer join
在t1表中target_appl_code允许为空,source_appl_code不允许为空
用inner join的时候少记录,但是用left outer join的时候,记录不少了
但是少掉的那些记录中,原本source_appl_code不为空的,但现在也为空了
CREATE TABLE tl_001 (rid INTEGER,code1 INTEGER,code2 INTEGER)INSERT INTO tl_001
SELECT 1,1,1 FROM dual
UNION
SELECT 2,2,1 FROM dual
UNION
SELECT 3,NULL,3 FROM dualCREATE TABLE tl_002(code INTEGER);
CREATE TABLE tl_003(code INTEGER);INSERT INTO tl_002
SELECT 1 FROM dual
UNION
SELECT 2 FROM dualINSERT INTO tl_003
SELECT 1 FROM dual
UNION
SELECT 3 FROM dualSELECT * FROM tl_001 a LEFT OUTER JOIN tl_002 b
ON a.code1=b.code
INNER JOIN tl_003 c
ON a.code2=c.code结果
rid tl_002。code tl_003。code
1 1 1
2 2 1
3 3
谢谢啊!
还是有点问题,
select * from
(select h.* , rownum r
from
(
a.plat_msg_id,b.appl_name as target_appl_name,
d.message_type_name as target_appl_message_type_name,
c.appl_name as source_appl_name,
e.process_name as process_code
from t1 a inner join t2 b
on a.target_appl_code=b.appl_code
inner join t3 d
on a.target_appl_message_type_code=d.message_type_code
inner join t2 c
on a.source_appl_code=c.appl_code
inner join t4 e
on a.process_code=e.process_code)
h
where rownum <= {endRow} and {sql: whereStr})j
where r >= {startRow}
这其中{sql: whereStr}我是在拼查询子句,我想用a.plat_msg_id去替换{sql: whereStr}
但是报错,"a"."plat_msg_id"无效标识---