除非还指定了TOP，OFFSET或FOR XML，否则ORDER BY子句在视图，内联函数，派生表，子查询和公用表表达式中无效。 [英] The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

问题描述

错误

Quote：

ORDER BY子句在视图，内联函数，派生表中无效，子查询和公用表表达式，除非还指定了TOP，OFFSET或FOR XML。

我想每次都得到随机问题所以我使用< pre lang =sql> order by newid（）
< pre lang = < span class =code-string> sql>选择 top （< span class =code-digit> 4 ）* 来自
（
选择 q.Description，q.Ques_Id，ac.ch_number，ac.Choice
<跨度类= 代码关键字>从问题<跨度类= 代码关键字> as q，course as c，Ques_Choice as ac
其中< /跨度>（q.crs_id = c.crs_id）<跨度类= 代码关键字>和（q.Ques_Id = ac.Ques_Id）<跨度类= 代码关键字>和（c.crs_name = ' oop' 和 q.type = 2）
order by newid（）
） as alr

pivot
（
min（选择）
for ch_number in （a，b，c，d）
） as pvt

union
select top （ 2 ）* 来自
（
<跨度类= 代码关键字>选择 q.Description，q.Ques_Id，ac.ch_number，ac.Choice
<跨度类= 代码关键字>从问题 as q，course as c，Ques_Choice as ac
其中（q.crs_id = c.crs_id）和（q.Ques_Id = ac .Ques_Id）和（c.crs_name = ' oop' 和 q.type = 1）
order by newid（）
） as alr

pivot
（
min（选择）
ch_number in （a，b，c，d）
）中的作为 PVT< /预>

解决方案

别接受我的话，但我似乎记得当我使用UNION查询时，ORDER BY来自UNION组合，因此appl对所有元素都很感兴趣换句话说，只有一个ORDER BY而不是两个，每个部分一个。

the error

Quote:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

I want to get random question every time so i use

order by newid()
<pre lang="sql">select top (4)* from
 (
       select  q.Description,q.Ques_Id,ac.ch_number,ac.Choice
       from Question as q,  course as c,Ques_Choice as ac
        where (q.crs_id = c.crs_id) and (q.Ques_Id=ac.Ques_Id)and (c.crs_name='oop' and q.type=2)
        order by newid()
  ) as alr

pivot
  (
      min(Choice)
      for ch_number in (a,b,c,d)
  ) as pvt

  union
  select top (2)* from
 (
       select  q.Description,q.Ques_Id,ac.ch_number,ac.Choice
       from Question as q,  course as c,Ques_Choice as ac
        where (q.crs_id = c.crs_id) and (q.Ques_Id=ac.Ques_Id)and (c.crs_name='oop' and q.type=1)
        order by newid()
  ) as alr

pivot
  (
      min(Choice)
      for ch_number in (a,b,c,d)
  ) as pvt</pre>

解决方案

Don't take my word for it but I seem to remember that when I used a UNION query the ORDER BY came after the UNION combination thus applying to all elements. In other words there was only one ORDER BY and not two, one for each part.

这篇关于除非还指定了TOP，OFFSET或FOR XML，否则ORDER BY子句在视图，内联函数，派生表，子查询和公用表表达式中无效。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！