817中 SQL> select 1,(select 2 from dual) from dual; 1 (SELECT2FROMDUAL) ---------- ----------------- 1 2SQL>
to snowy_howe(天下有雪)SELECT ajbh AS A, (SELECT 1 FROM xckcbc WHERE ajbh=CC.ajbh AND (zagcmsjyj<>'' OR zagcmsjyj IS NOT NULL) AND ROWNUM=1) AS B, (SELECT 1 FROM tblxchjwz WHERE ajbh=CC.ajbh AND sortno='57' AND ROWNUM=1) AS C, (SELECT 1 FROM xckcblcg WHERE ajbh=CC.ajbh AND ROWNUM=1) AS D, (SELECT 1 FROM tblmattersave WHERE caseno=CC.ajbh AND ROWNUM=1) AS E, (SELECT 1 FROM tblcheckupbak WHERE caseno=CC.ajbh AND ROWNUM=1) AS F, (SELECT 1 FROM tblcheckup WHERE caseno=CC.ajbh AND targets<>'其他' AND ROWNUM=1) AS G FROM xckcbc CC group by ajbh很明确的指出,子查询并非独立的,它和外部查询是相关的------ajbh=CC.ajbh
我是用816的 SQL> create table a(a varchar2(10));Table createdSQL> create table b(a varchar2(10));Table createdSQL> insert into a values('asd');1 row insertedSQL> insert into b values('asd');1 row insertedSQL> commit;Commit completeSQL> select a ,(select a from b) from a;A (SELECTAFROMB) ---------- -------------- asd asdSQL> insert into b values('asd23');1 row insertedSQL> commit;Commit completeSQL> select a ,(select a from b) from a;select a ,(select a from b) from aORA-01427: 单行子查询返回多于一个行SQL> select a.a,b.a from a,b where b.a=a.a(+);A A ---------- ---------- asd asd asd23
用这种select a.a,b.a from a,b where b.a=a.a(+); 不可以吗?
这个我知道,问题的关键是如何转换子查询的输出列.
SQL> select 1,(select 2 from dual) from dual; 1 (SELECT2FROMDUAL)
---------- -----------------
1 2SQL>
ajbh AS A,
(SELECT 1 FROM xckcbc WHERE ajbh=CC.ajbh AND (zagcmsjyj<>'' OR zagcmsjyj IS NOT NULL) AND ROWNUM=1) AS B,
(SELECT 1 FROM tblxchjwz WHERE ajbh=CC.ajbh AND sortno='57' AND ROWNUM=1) AS C,
(SELECT 1 FROM xckcblcg WHERE ajbh=CC.ajbh AND ROWNUM=1) AS D,
(SELECT 1 FROM tblmattersave WHERE caseno=CC.ajbh AND ROWNUM=1) AS E,
(SELECT 1 FROM tblcheckupbak WHERE caseno=CC.ajbh AND ROWNUM=1) AS F,
(SELECT 1 FROM tblcheckup WHERE caseno=CC.ajbh AND targets<>'其他' AND ROWNUM=1) AS G
FROM xckcbc CC group by ajbh很明确的指出,子查询并非独立的,它和外部查询是相关的------ajbh=CC.ajbh
SQL> create table a(a varchar2(10));Table createdSQL> create table b(a varchar2(10));Table createdSQL> insert into a values('asd');1 row insertedSQL> insert into b values('asd');1 row insertedSQL> commit;Commit completeSQL> select a ,(select a from b) from a;A (SELECTAFROMB)
---------- --------------
asd asdSQL> insert into b values('asd23');1 row insertedSQL> commit;Commit completeSQL> select a ,(select a from b) from a;select a ,(select a from b) from aORA-01427: 单行子查询返回多于一个行SQL> select a.a,b.a from a,b where b.a=a.a(+);A A
---------- ----------
asd asd
asd23
不可以吗?
若大个csdn就只有些说oracle能sql server不能的假NB,连个查询的问题都没什么人关注..这个查询就这么难吗???没人回答我就把分给自己了,可别怪我倒分哦.
版主该休息了..