--这个sql语句为什么会错误selecttop2"Temp".*,"D".dname"DeptName","S".grade"SalGrade"from(select"E".deptno"deptNumber",floor(avg("E".sal))"avgSal",count(*)"PersonNumber"fro...
--这个sql语句为什么会错误
select top 2 "Temp".*, "D".dname "DeptName", "S".grade "SalGrade"
from
(
select "E".deptno "deptNumber",floor(avg("E".sal)) "avgSal",count(*) "PersonNumber"
from emp "E"
where "E".sal > 2000
group by "E".deptno
order by "Temp"."avgSal" desc --这里的差别
) "Temp"
join salgrade "S" on "Temp"."avgSal" >= "S".losal and "Temp"."avgSal" <= "S".hisal
join dept "D" on "D".deptno = "Temp"."deptNumber"
--这个sql语句为什么会正确
select top 2 "Temp".*, "D".dname "DeptName", "S".grade "SalGrade"
from
(
select "E".deptno "deptNumber",floor(avg("E".sal)) "avgSal",count(*) "PersonNumber"
from emp "E"
where "E".sal > 2000
group by "E".deptno
) "Temp"
join salgrade "S" on "Temp"."avgSal" >= "S".losal and "Temp"."avgSal" <= "S".hisal
join dept "D" on "D".deptno = "Temp"."deptNumber"
order by "Temp"."avgSal" desc --这里的差别
select top 2 "Temp".*, "D".dname "DeptName", "S".grade "SalGrade"
from
(
select "E".deptno "deptNumber",floor(avg("E".sal)) "avgSal",count(*) "PersonNumber"
from emp "E"
where "E".sal > 2000
group by "E".deptno
) "Temp"
join salgrade "S" on "Temp"."avgSal" >= "S".losal and "Temp"."avgSal" <= "S".hisal
join dept "D" on "D".deptno = "Temp"."deptNumber"
order by "Temp"."avgSal" desc
//注意:上面两个语句的差别是 order by "avgSal" desc 放在子select末尾还是外部select末尾
错误消息:
消息 1033,级别 15,状态 1,第 10 行
除非另外还指定了 TOP 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
展开