在一個表中重復查詢中一個fields對應多個value的問題 已解決一部分,請大家幫再看看!!!謝謝
原貼：
http://community.csdn.net/Expert/topic/3608/3608660.xml?temp=.1835749
很感謝大家的幫忙，基本得到部分想得到的數據，尤其感謝ORARichard(没钱的日子好难过啊)
SQL>select collection_id,character1 ,character3 from Qa_Results where collection_id in
( select collection_id from Qa_Results where RECEIPT_NUM = '304110034')
order by collection_id,receipt_num
COLLECTION_ID CHARACTER1 CHARACTER3
65869 R 拒絕
65869 S
65869 TBD-04 主缺點
65872 R 拒絕
65872 S
65872 TFO-99 次缺點
65886 A 接受
65889 A 接受
我想通過一個select語句得一下如下的結果：(新增三個fields:s、T、缺點)
COLLECTION_ID CHARACTER1 CHARACTER3 s T 缺點
65869 R 拒絕 s TBD-04 主缺點
65872 R 拒絕 s TFO-99 次缺點
65886 A 接受
65889 A 接受
請問怎麼做呢？(S 列中的值不是固定的如果為接受s,t,缺點都為空)
下面的解法：fields S 的值能夠從數據庫提出來,但fields“T、缺點”的值得不到(查詢結果為空)
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(+)
问题点数：100、回复次数：7Top
1 楼dy18（黎明之目）回复于 2004-12-02 20:46:07 得分 0
也就是：Q3.character1,Q3.character3 的值代不出來！！！
請大家幫看一下！
謝謝
Top
2 楼ORARichard（没钱的日子......）回复于 2004-12-02 21:12:09 得分 100
对不起，原贴中没注意写错一个地方
--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(+)
Top
3 楼ORARichard（没钱的日子......）回复于 2004-12-02 21:13:45 得分 0
(select collection_id,character1 from qa_results where (CHARACTER3 is null or CHARACTER3='') and RECEIPT_NUM = '304110034') q2,
这个结果集中条件应该是
where (... or ...) and ...
而原来那句写成了 where ... or ... and ...Top
4 楼dy18（黎明之目）回复于 2004-12-02 21:47:23 得分 0
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(+)
這樣後一Q2.character1,Q3.character1,Q3.character3 的值都沒有了？？？Top
5 楼ORARichard（没钱的日子......）回复于 2004-12-02 21:57:23 得分 0
你把三个结果集分别单独做一下，看看都能取到什么样的结果集，然后把结果贴出来Top
6 楼ORARichard（没钱的日子......）回复于 2004-12-02 22:34:17 得分 0
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 !='拒絕' and length(character3)>0 and character3 !='接受') 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='接受';Top
7 楼ORARichard（没钱的日子......）回复于 2004-12-02 22:36:35 得分 0
--retry:
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 !='拒絕' and length(character3)>0 and character3 !='接受') 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='接受' and RECEIPT_NUM = '304110034';Top
