select t.zjmc as CJFA,t.obj_id as TID,i.obj_id as IID,
(select sjymc from mw_app.jk_sjydy where obj_id = i.sjyid) as CJDX,
(select rkdx from mw_app.jk_sjcj_item where t.obj_id = i.zjid) as RKDX,
(select cjms from mw_app.jk_sjcj_item where t.obj_id = i.zjid) as CJMS,
(select rznr from mw_app.pt_xt_log_item where obj_id = t.rzid) as RZ
from mw_app.jk_task_tec t,mw_app.jk_sjcj_item i where t.flid = 'ABEC3075-EA6B-4A36-BA0C-47CFFA228F32-00006' and t.obj_id = i.zjid
查询提示:单行子查询返回多个行
求高手解决
(select sjymc from mw_app.jk_sjydy where obj_id = i.sjyid) as CJDX,
(select rkdx from mw_app.jk_sjcj_item where t.obj_id = i.zjid) as RKDX,
(select cjms from mw_app.jk_sjcj_item where t.obj_id = i.zjid) as CJMS,
(select rznr from mw_app.pt_xt_log_item where obj_id = t.rzid) as RZ
from mw_app.jk_task_tec t,mw_app.jk_sjcj_item i where t.flid = 'ABEC3075-EA6B-4A36-BA0C-47CFFA228F32-00006' and t.obj_id = i.zjid
查询提示:单行子查询返回多个行
求高手解决
解决方案 »
- 入门oracle问题,select变量出来,来人帮忙
- 在2003下安装完Oracle10g,怎么才能双机热备
- 同时安装Oracle devoloper suit 10 与Oracle 10数据库的问题
- 只有ORADATA目录。请问怎样恢复数据?
- oracle10g与oracle9i
- 如何能够删除oracle中的整个的表.drop命令的格式该怎么写?(初学者)
- 一个SQL查询
- bdf档案转换成dmp档案问题
- 一段insert的sql 直接执行12分钟完成 放到pro里面跑90分钟然后报错,求解
- 求SQL语句
- asp.net mvc 加 ORCLE开发,多个客户端只有一个ORACLE SESSION
- .net连接oracle问题
(select rkdx from mw_app.jk_sjcj_item where t.obj_id = i.zjid) as RKDX,
(select cjms from mw_app.jk_sjcj_item where t.obj_id = i.zjid) as CJMS,
(select rznr from mw_app.pt_xt_log_item where obj_id = t.rzid) as RZ
一个一个distinct,测试出谁多了
--问题出在这两个地方
(select rkdx from mw_app.jk_sjcj_item where t.obj_id = i.zjid) as RKDX,
(select cjms from mw_app.jk_sjcj_item where t.obj_id = i.zjid) as CJMS,
--说明:
--这两个地方查询出来肯定不止一行,因为别名i和别名t分别是指的表mw_app.jk_sjcj_item和mw_app.jk_task_tec,你的条件只是做这两表的关联,和你的表mw_app.jk_sjcj_item并没有关联,也没有其他任何限制条件,你这和select*from mw_app.jk_sjcj_item没有区别。后面的条件都没限制你这个表的结果集。
--建议你把业务理清。然后就是 像这样的查询太耗性能了。可以优化
--应该这样
select
t.zjmc as CJFA,
t.obj_id as TID,
i.obj_id as IID,
j.sjymc as CJDX,
i.rkdx as RKDX,
i.cjms as CJMS,
x.rznr as RZ
from mw_app.jk_task_tec t,
mw_app.jk_sjcj_item i,
mw_app.jk_sjydy j,
mw_app.pt_xt_log_item x
where t.flid = 'ABEC3075-EA6B-4A36-BA0C-47CFFA228F32-00006'
and t.obj_id = i.zjid
and j.obj_id = i.sjyid
and t.obj_id = i.zjid
and t.obj_id = i.zjid
and x.obj_id = t.rzid
1.ABEC3075-EA6B-4A36-BA0C-47CFFA228F32-00006是传进来的的参数,根据该参数和mw_app.jk_task_tec表中的字段flid的对应关系查询zjmc,bz,obj_id,rzid
2.根据1步查询的obj_id和mw_app.jk_sjcj_item表中的字段zjid的对应关系查询sjyid,rkdx,cjms
3.根据1步中查询的rzid和mw_app.pt_xt_log_item表的obj_id对应关系查询rznr
4.根据2步中查询的sjyid和mw_app.jk_sjydy表中的o字段bj_id的对应关系查询sjymc
楼上的查询出来的结果为空,但是实际库中是有数据的
例 表A 表B 表C
1)有结果 关联到数据 关联结果为空 = 表A + 表B 数据展示
2)有结果 关联到数据 关联结果为空 = 展示为空