怎么会提示 对象名无效？

SELECT TEMP.sid, TEMP.sname
FROM (SELECT E.sid, num=COUNT(*), S.sname
FROM [databasePrinciple-1].[dbo].[Enroll] E, [databasePrinciple-1] .[dbo] .Student S
WHERE E.sid = S.sid
GROUP BY E.sid, S.sname) AS TEMP
WHERE TEMP.num = (SELECT MAX(TEMP.num) FROM TEMP)

以上的SQL语句提示对象名TEMP无效，如果我把(SELECT MAX(TEMP.num) FROM TEMP)改成一个固定值3，则该代码可以正确执行。如下：
SELECT TEMP.sid, TEMP.sname
FROM (SELECT E.sid, num=COUNT(*), S.sname
FROM [databasePrinciple-1].[dbo].[Enroll] E, [databasePrinciple-1] .[dbo] .Student S
WHERE E.sid = S.sid
GROUP BY E.sid, S.sname) AS TEMP
WHERE TEMP.num = 3
如果TEMP对象找不到，那么它又怎么能识别TEMP.num呢？
真心求教！

SQL code

SELECT TEMP.sid, TEMP.sname
FROM (SELECT E.sid, num=COUNT(*), S.sname
FROM [databasePrinciple-1].[dbo].[Enroll] E, [databasePrinciple-1] .[dbo] .Student S
WHERE E.sid = S.sid
GROUP BY E.sid, S.sname) AS TEMP
WHERE TEMP.num = (SELECT MAX(num) FROM TEMP)

SQL code

SELECT top 1 TEMP.sid, TEMP.sname
FROM (SELECT E.sid, num=COUNT(*), S.sname
FROM [databasePrinciple-1].[dbo].[Enroll] E, [databasePrinciple-1] .[dbo] .Student S
WHERE E.sid = S.sid
GROUP BY E.sid, S.sname) AS TEMP order by num desc

temp 是个记录集,不是一个表,你不能在另一个子查询中引用前一子查询的数据集.如果一定要引用,则必须在from 后面写全这个子查询的全部语句.

2005以上,可以用公用表达式来处理:
SQL code

;with temp as(
SELECT E.sid, num=COUNT(*), S.sname
FROM [databasePrinciple-1].[dbo].[Enroll] E, [databasePrinciple-1] .[dbo] .Student S
WHERE E.sid = S.sid
GROUP BY E.sid, S.sname
)select sid,sname from temp where num=(select MAX(num) from tempdb)

2000,可以先将子查询得到的结果集保存到临时表 (select .... into # from ..),然后用上面类似的语法从临时表中去查.

新增一列排序列
SQL code

select *
from 
(
SELECT E.sid, num=COUNT(*), S.sname,ROW_NUMBER()over(order by COUNT(*) desc) as row
FROM [databasePrinciple-1].[dbo].[Enroll] E, [databasePrinciple-1] .[dbo] .Student S
WHERE E.sid = S.sid
GROUP BY E.sid, S.sname
)t
where row =3

前面写得有点错:
SQL code

;with temp as(
SELECT E.sid, num=COUNT(*), S.sname
FROM [databasePrinciple-1].[dbo].[Enroll] E, [databasePrinciple-1] .[dbo] .Student S
WHERE E.sid = S.sid
GROUP BY E.sid, S.sname
)select sid,sname from temp where num=(select MAX(num) from temp)

谢谢你的回答，我想问下，如果不用临时表，还有其他的解决方法么？

--如果最大的值只有一个:
SQL code

SELECT top 1 E.sid, num=COUNT(*), S.sname
FROM [databasePrinciple-1].[dbo].[Enroll] E, [databasePrinciple-1] .[dbo] .Student S
WHERE E.sid = S.sid
GROUP BY E.sid, S.sname
order by num desc

--如果最大的值有多个:
SQL code

select m.* from
(
SELECT E.sid, num=COUNT(*), S.sname
FROM [databasePrinciple-1].[dbo].[Enroll] E, [databasePrinciple-1] .[dbo] .Student S
WHERE E.sid = S.sid
GROUP BY E.sid, S.sname
) m where m.num = 
(
SELECT top 1 num=COUNT(*)
FROM [databasePrinciple-1].[dbo].[Enroll] E, [databasePrinciple-1] .[dbo] .Student S
WHERE E.sid = S.sid
GROUP BY E.sid, S.sname
order by num desc
)