字段是不是没给全,不太好写,自己再改改select Q1.collection_id,Q1.character1 ,Q1.character3 ,Q2.character1,Q3.character1,Q3.character3 from Qa_Results Q1,Qa_Results Q2,Qa_Results Q3 where Q1.RECEIPT_NUM = '304110034' and Q1.character1 in('A','R') and Q2.character1 in('S') and Q3.character1 like '%-%' and Q1.collection_id = Q2.collection_id(+) and Q1.collection_id = Q3.collection_id(+)
select Q1.collection_id,Q1.character1 ,Q1.character3 ,Q2.character1,Q3.character1,Q3.character3 from (select collection_id,character1 ,character3 from qa_results where CHARACTER3='拒絕') q1, (select collection_id,character1 from qa_results where CHARACTER3 is null or CHARACTER3='') q2, (select collection_id,character1 ,character3 from qa_results where CHARACTER3 not in ('拒絕','接受','',null) q3 where q1.collection_id=q2.collection_id(+) and q1.collection_id=q3.collection_id(+) union select collection_id,character1 ,character3,null,null,null from qa_results where CHARACTER3='接受';
前面的少了个条件:--try: select Q1.collection_id,Q1.character1 ,Q1.character3 ,Q2.character1,Q3.character1,Q3.character3 from (select collection_id,character1 ,character3 from qa_results where CHARACTER3='拒絕' and RECEIPT_NUM = '304110034') q1, (select collection_id,character1 from qa_results where CHARACTER3 is null or CHARACTER3='' and RECEIPT_NUM = '304110034') q2, (select collection_id,character1 ,character3 from qa_results where CHARACTER3 not in ('拒絕','接受','',null and RECEIPT_NUM = '304110034') q3 where q1.collection_id=q2.collection_id(+) and q1.collection_id=q3.collection_id(+) union select collection_id,character1 ,character3,null,null,null from qa_results where CHARACTER3='接受';
那要找出?(s、T、缺點三個fields所在表 和Qa_Results之间的关联
關係為:COLLECTION_ID 相同的為一個關係(但相同的有一個為key---對應的RECEIPT_NUM = '304110034' 那個個COLLECTION_ID
)先謝謝 hqskoala(大晟) 我再試試
from (select collection_id,character1 ,character3 from qa_results where CHARACTER3='拒絕') q1,
(select collection_id,character1 from qa_results where CHARACTER3 is null or CHARACTER3='') q2,
(select collection_id,character1 ,character3 from qa_results where CHARACTER3 not in ('拒絕','接受','',null) q3
where q1.collection_id=q2.collection_id(+) and q1.collection_id=q3.collection_id(+)
union
select collection_id,character1 ,character3,null,null,null from qa_results where CHARACTER3='接受';
select Q1.collection_id,Q1.character1 ,Q1.character3 ,Q2.character1,Q3.character1,Q3.character3
from (select collection_id,character1 ,character3 from qa_results where CHARACTER3='拒絕' and RECEIPT_NUM = '304110034') q1,
(select collection_id,character1 from qa_results where CHARACTER3 is null or CHARACTER3='' and RECEIPT_NUM = '304110034') q2,
(select collection_id,character1 ,character3 from qa_results where CHARACTER3 not in ('拒絕','接受','',null and RECEIPT_NUM = '304110034') q3
where q1.collection_id=q2.collection_id(+) and q1.collection_id=q3.collection_id(+)
union
select collection_id,character1 ,character3,null,null,null from qa_results where CHARACTER3='接受';