表Question(id,question)
表Answer(QuestionID,Answer,isBest)
一条Question可以有多条Answer,但是IsBest为1的数据只能一条,
现要查询一条Question对应一条Answer,(如果Answer有Isbest =1 的就选,没有就选第一条,没有数据就为空)SqlServer
表Answer(QuestionID,Answer,isBest)
一条Question可以有多条Answer,但是IsBest为1的数据只能一条,
现要查询一条Question对应一条Answer,(如果Answer有Isbest =1 的就选,没有就选第一条,没有数据就为空)SqlServer
select *,
isnull(
(select Answer from Answer where QuestionID=a.id and isBest=1)
,
isnull((select top 1 Answer from Answer where QuestionID=a.id order by QuestionID),'')
) Answer
from Question a
from Question a
left join Answer b on a.id=b.QuestionID and b.IsBest=1
from Question a
left join
(select * from Answer where isBest=1) b on a.id=b.QuestionID
A.*,
Answer=isnull((select top 1 Answer from Answer b where B.QuestionID=a.id order by isBest DESC, QuestionID),'')
from Question A