以下是一个条件的查询,可以使用union all 进行四个查询的合并,除此外,还是否有更简洁的写法:
select top 24 * from (select distinct(qq.questionid) questionid,qpv.paperviewid,qq.limitminute,qq.splitscore ,qq.optnum,qq.creator,qq.modifyStatus,qq.parentid,qq.questypeid ,qq.quesviewtype,qq.content,qq.answer,qq.analysis,qq.score,qq.status ,convert(varchar(100),qq.createtime,20) createtime from qz_question qq with (nolock) join qz_paper_question qpq with (nolock) on qq.questionid = qpq.questionid join qz_paper_view qpv with (nolock) on qpq.paperid = qpv.paperid join qz_point_question qpoq with (nolock) on qq.questionid = qpoq.questionid join qz_point qpo with (nolock) on qpoq.pointid = qpo.pointid where qpv.paperviewid = 27714 and ((qq.parentid != 0) or (qq.parentid = 0 and qq.answer is not null)) and qpo.pointlistid = 572 and qq.quesviewtype = 1 ) a order by newID()
[解决办法]
试试这个:
select * from (select distinct(qq.questionid) questionid,qpv.paperviewid,qq.limitminute,qq.splitscore ,qq.optnum,qq.creator,qq.modifyStatus,qq.parentid,qq.questypeid ,qq.quesviewtype,qq.content,qq.answer,qq.analysis,qq.score,qq.status ,convert(varchar(100),qq.createtime,20) createtime ,ROW_NUMBER(PARTITION by qq.quesviewtype order by NEWID()) rownum from qz_question qq with (nolock) join qz_paper_question qpq with (nolock) on qq.questionid = qpq.questionid join qz_paper_view qpv with (nolock) on qpq.paperid = qpv.paperid join qz_point_question qpoq with (nolock) on qq.questionid = qpoq.questionid join qz_point qpo with (nolock) on qpoq.pointid = qpo.pointid where qpv.paperviewid = 27714 and ((qq.parentid != 0) or (qq.parentid = 0 and qq.answer is not null)) and qpo.pointlistid = 572 --and qq.quesviewtype = 1 ) a where rownum <= CASE qq.quesviewtype WHEN 1 THEN 24 WHEN 2 THEN 15 WHEN 3 THEN 10 WHEN 4 THEN 18 END
[解决办法]
报什么错呢?
关键字"by"附近有语法错误
改成这个试试:
select * from (select distinct(qq.questionid) questionid,qpv.paperviewid,qq.limitminute,qq.splitscore ,qq.optnum,qq.creator,qq.modifyStatus,qq.parentid,qq.questypeid ,qq.quesviewtype,qq.content,qq.answer,qq.analysis,qq.score,qq.status ,convert(varchar(100),qq.createtime,20) createtime ,ROW_NUMBER() over(PARTITION by qq.quesviewtype order by NEWID()) rownum from qz_question qq with (nolock) join qz_paper_question qpq with (nolock) on qq.questionid = qpq.questionid join qz_paper_view qpv with (nolock) on qpq.paperid = qpv.paperid join qz_point_question qpoq with (nolock) on qq.questionid = qpoq.questionid join qz_point qpo with (nolock) on qpoq.pointid = qpo.pointid where qpv.paperviewid = 27714 and ((qq.parentid != 0) or (qq.parentid = 0 and qq.answer is not null)) and qpo.pointlistid = 572 --and qq.quesviewtype = 1 ) a where rownum <= CASE qq.quesviewtype WHEN 1 THEN 24 WHEN 2 THEN 15 WHEN 3 THEN 10 WHEN 4 THEN 18 END