SELECT
FI.SVCID,
FI.GROUPNO,
FI.ENQUETE_NO,
QL.QUESTION_NO,
QL.QUESTION,
QL.QTYPE,
CL.CHOICENO,
CL.CHOICETXT,
FI.STARTDT,
FI.ENDDT
FROM
INFO FI
LEFT OUTER JOIN QUESTION_LIST QL
ON FI.ENQUETE_NO = QL.ENQUETE_NO
LEFT OUTER JOIN CHOICE_LIST CL
ON FI.ENQUETE_NO = CL.ENQUETE_NO
AND QL.QUESTION_NO = CL.QUESTION_NO
WHERE
FI.SVCID = 'XXX'
AND
FI.GROUPNO = 53
AND
FI.STARTDT < SYSDATE
AND
FI.ENDDT > SYSDATE 把left join的写法换成+的写法,如何弄?
FI.SVCID,
FI.GROUPNO,
FI.ENQUETE_NO,
QL.QUESTION_NO,
QL.QUESTION,
QL.QTYPE,
CL.CHOICENO,
CL.CHOICETXT,
FI.STARTDT,
FI.ENDDT
FROM
INFO FI
LEFT OUTER JOIN QUESTION_LIST QL
ON FI.ENQUETE_NO = QL.ENQUETE_NO
LEFT OUTER JOIN CHOICE_LIST CL
ON FI.ENQUETE_NO = CL.ENQUETE_NO
AND QL.QUESTION_NO = CL.QUESTION_NO
WHERE
FI.SVCID = 'XXX'
AND
FI.GROUPNO = 53
AND
FI.STARTDT < SYSDATE
AND
FI.ENDDT > SYSDATE 把left join的写法换成+的写法,如何弄?
FROM INFO FI ,
QUESTION_LIST QL ,
CHOICE_LIST CL
WHERE FI.ENQUETE_NO = QL.ENQUETE_NO(+)
AND FI.ENQUETE_NO = CL.ENQUETE_NO(+)
...
FI.SVCID, FI.GROUPNO, FI.ENQUETE_NO, QL.QUESTION_NO,
QL.QUESTION, QL.QTYPE, CL.CHOICENO, CL.CHOICETXT,
FI.STARTDT, FI.ENDDT
FROM INFO FI, QUESTION_LIST QL, CHOICE_LIST CL
WHERE FI.ENQUETE_NO = QL.QL.ENQUETE_NO(+)
AND FI.ENQUETE_NO = CL.ENQUETE_NO(+)
AND FI.SVCID = 'XXX'
AND FI.GROUPNO = 53
AND FI.STARTDT < SYSDATE
AND FI.ENDDT > SYSDATE;
关于外连接的归纳如下:
在使用老的SQL标准的外连接查询语句时,(+)放在=的左边或右边分别对应叫做右外连接和左外连接;进行左外连接时,左面的表的数据全部显示出来;(+)放在哪个表的后面,就会将另一表中所有记录显示出来。
FI.SVCID,
FI.GROUPNO,
FI.ENQUETE_NO,
QL.QUESTION_NO,
QL.QUESTION,
QL.QTYPE,
CL.CHOICENO,
CL.CHOICETXT,
FI.STARTDT,
FI.ENDDT
FROM
INFO FI
,QUESTION_LIST QL
,CHOICE_LIST CL
WHERE
FI.ENQUETE_NO = QL.ENQUETE_NO(+)
AND
FI.ENQUETE_NO = CL.ENQUETE_NO(+)
AND
QL.QUESTION_NO = CL.QUESTION_NO(+)
AND
FI.SVCID = 'XXX'
AND
FI.GROUPNO = 53
AND
FI.STARTDT < SYSDATE
AND
FI.ENDDT > SYSDATE
--说明表的链接顺序有问题 FI.ENQUETE_NO (+) = QL.ENQUETE_NO
AND
FI.ENQUETE_NO = CL.ENQUETE_NO(+)
AND
QL.QUESTION_NO = CL.QUESTION_NO(+)
如果遇到那个,就把这个左链接变成右链接,即可!
FI.SVCID,
FI.GROUPNO,
FI.ENQUETE_NO,
QL.QUESTION_NO,
QL.QUESTION,
QL.QTYPE,
CL.CHOICENO,
CL.CHOICETXT,
FI.STARTDT,
FI.ENDDT
FROM
INFO FI
,QUESTION_LIST QL
,CHOICE_LIST CL
WHERE
FI.ENQUETE_NO = QL.ENQUETE_NO(+)
AND
FI.ENQUETE_NO = CL.ENQUETE_NO(+)
AND
CL.QUESTION_NO = QL.QUESTION_NO(+)
AND
FI.SVCID = 'XXX'
AND
FI.GROUPNO = 53
AND
FI.STARTDT < SYSDATE
AND
FI.ENDDT > SYSDATE 改了一下,你试试!
SELECT FI.SVCID, FI.GROUPNO, FI.ENQUETE_NO, QL.QUESTION_NO, QL.QUESTION, QL.QTYPE, CL.CHOICENO, CL.CHOICETXT, FI.STARTDT, FI.ENDDT
FROM INFO FI , QUESTION_LIST QL ,CHOICE_LIST CL
WHERE FI.ENQUETE_NO *= QL.ENQUETE_NO AND FI.ENQUETE_NO *= CL.ENQUETE_NO AND CL.QUESTION_NO *=QL.QUESTION_NO
FI.SVCID = 'XXX' AND FI.GROUPNO = 53 AND FI.STARTDT < SYSDATE AND FI.ENDDT > SYSDATE
SELECT FI.SVCID, FI.GROUPNO, FI.ENQUETE_NO, QL.QUESTION_NO, QL.QUESTION, QL.QTYPE, CL.CHOICENO, CL.CHOICETXT, FI.STARTDT, FI.ENDDT
FROM INFO FI , QUESTION_LIST QL ,CHOICE_LIST CL
WHERE FI.ENQUETE_NO = QL.ENQUETE_NO(+) AND FI.ENQUETE_NO = CL.ENQUETE_NO(+) AND CL.QUESTION_NO=QL.QUESTION_NO(+)
FI.SVCID = 'XXX' AND FI.GROUPNO = 53 AND FI.STARTDT < SYSDATE AND FI.ENDDT > SYSDATE
select FICL.SVCID, FICL.GROUPNO, FICL.ENQUETE_NO, FICL.STARTDT, FICL.ENDDT , FICL.CHOICENO, FICL.CHOICETXT,FICL.QUESTION_NO
from
(
SELECT FI.SVCID, FI.GROUPNO, FI.ENQUETE_NO, FI.STARTDT, FI.ENDDT , CL.CHOICENO, CL.CHOICETXT,CL.QUESTION_NO
from INFO FI ,CHOICE_LIST CL
where FI.ENQUETE_NO = CL.ENQUETE_NO(+) and FI.SVCID = 'XXX' AND FI.GROUPNO = 53 AND FI.STARTDT < SYSDATE AND FI.ENDDT > SYSDATE ) FICL,
QUESTION_LIST QL
where FICL.ENQUETE_NO= QL.ENQUETE_NO(+) and FICL.QUESTION_NO=QL.QUESTION_NO(+)
select FICL.SVCID, FICL.GROUPNO, FICL.ENQUETE_NO, FICL.STARTDT, FICL.ENDDT , FICL.CHOICENO, FICL.CHOICETXT,FICL.QUESTION_NO, QL.QUESTION_NO, QL.QUESTION, QL.QTYPE
from
(
SELECT FI.SVCID, FI.GROUPNO, FI.ENQUETE_NO, FI.STARTDT, FI.ENDDT , CL.CHOICENO, CL.CHOICETXT,CL.QUESTION_NO
from INFO FI ,CHOICE_LIST CL
where FI.ENQUETE_NO = CL.ENQUETE_NO(+) and FI.SVCID = 'XXX' AND FI.GROUPNO = 53 AND FI.STARTDT < SYSDATE AND FI.ENDDT > SYSDATE ) FICL,
QUESTION_LIST QL
where FICL.ENQUETE_NO= QL.ENQUETE_NO(+) and FICL.QUESTION_NO=QL.QUESTION_NO(+)
select * from a,b where a.id=b.id(+)
OK
第二步
select * from a,b,c where a.id=b.id(+) and a.id=c.id(+)
AND FI.ENQUETENO = QL.ENQUETENO(+)
AND QL.ENQUETENO = CL.ENQUETENO(+)
AND QL.QUESTIONNO = CL.QUESTIONNO让QL做主表来左关联FI和CL,查询结果一样。