优化问题哪位高手能帮我优化一下

tautaulee 2010-05-06 05:48:06

select a.userid,a.type,a1.title as '题目' ,
case when a.useranswer='A' then a1.answerA
when a.useranswer='B' then a1.answerB
when a.useranswer='C' then a1.answerC
when a.useranswer='D' then a1.answerD end as '答案'
from useranswer as a inner join SingleProblem as a1 on a.titleid=a1.id where a.type='单选题'
union
select a.userid,a.type,b.title,
case when a.useranswer='A' then (b.answerA)
when a.useranswer='AB' then (b.answerA+' '+b.answerB)
when a.useranswer='ABC' then (b.answerA+' '+b.answerB+' '+b.answerC)
when a.useranswer='ABCD' then (b.answerA+' '+b.answerB+' '+b.answerC+' '+b.answerD)
when a.useranswer='B' then (b.answerB)
when a.useranswer='BC' then (b.answerB+' '+b.answerC)
when a.useranswer='BCD' then (b.answerB+' '+b.answerC+' '+b.answerD)
when a.useranswer='C' then (b.answerC)
when a.useranswer='CD' then (b.answerC+' '+b.answerD)
when a.useranswer='D' then (b.answerD)
end as '答案'
from useranswer as a inner join MultiProblem as b on a.titleid=b.id where a.type='多选题'
union
select a.userid,a.type,c.title,
case when a.useranswer='False' then '不是'
when a.useranswer='True' then '是'
end as '答案'
from useranswer as a inner join JudgeProblem as c on a.titleid=c.id where a.type='判断题'
union
select a.userid,a.type,d.fronttitle+'___'+d.backtitle,
case when a.useranswer!='qwertypasdfx' then d.answer
end as '答案'
from useranswer as a inner join FillBlankProblem as d on a.titleid=d.id where a.type='填空题'
union
select a.userid,a.type,e.title,
case when a.useranswer!='zxdcsfrt' then e.answer
end as '答案'
from useranswer as a inner join QuestionProblem as e on a.titleid=e.id where a.type='问答题'

...全文

155 15 打赏收藏转发到动态举报

写回复

用AI写文章

15 条回复

切换为时间正序

请发表友善的回复…

发表回复

feixianxxx 2010-05-08

打赏
举报

[Quote=引用 11 楼 tautaulee 的回复:]

引用 2 楼 garnett_kg 的回复:
你结不结贴先？！

你说结我就结怎么个解决法
[/Quote]
.....理解能力稍微差了点....

rose_stone7708 2010-05-07

打赏
举报

同意13楼朋友说的做法，但是我建议使用内连接，即就是使用 Inner Join 方式，因为在所有的链接中，只有内连接的性能最高，确定时内连接容易丢失一下错误数据，使用户一时找不到数据的错误地方在那里，这是它缺点，所以在使用时自己注意一下Where条件，就可以了

永生天地 2010-05-07

打赏
举报

改成左连接试试看



select a.userid,a.type,a1.title as '题目' ,

case when a.type='单选题'then

 case when a.useranswer='A' then a1.answerA   

 when a.useranswer='B' then a1.answerB  

 when a.useranswer='C' then a1.answerC  

 when a.useranswer='D' then a1.answerD end 

when a.type='多选题' then

 case when a.useranswer='A' then (b.answerA)

 when a.useranswer='AB' then (b.answerA+' '+b.answerB)

 when a.useranswer='ABC' then (b.answerA+' '+b.answerB+' '+b.answerC)

 when a.useranswer='ABCD' then (b.answerA+' '+b.answerB+' '+b.answerC+' '+b.answerD)

 when a.useranswer='B' then (b.answerB)

 when a.useranswer='BC' then (b.answerB+' '+b.answerC)

 when a.useranswer='BCD' then (b.answerB+' '+b.answerC+' '+b.answerD)

 when a.useranswer='C' then (b.answerC)

 when a.useranswer='CD' then (b.answerC+' '+b.answerD)

 when a.useranswer='D' then (b.answerD) end 

when a.type='判断题' then

 case when a.useranswer='False' then '不是'

 when a.useranswer='True' then '是' end

when a.type='填空题'  then

 case when a.useranswer!='qwertypasdfx' then d.answer end 

when a.type='问答题'  then

 case when a.useranswer!='zxdcsfrt' then e.answer end 

end as '答案'

from useranswer as a left join SingleProblem as a1 on a.titleid=a1.id  

left join MultiProblem as b on a.titleid=b.id 

left join JudgeProblem as c on a.titleid=c.id

left join FillBlankProblem as d on a.titleid=d.id

left join QuestionProblem as e on a.titleid=e.id