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呢?
真心求教!
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呢?
真心求教!
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)
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以上,可以用公用表达式来处理:
;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 ..),然后用上面类似的语法从临时表中去查.
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
;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)
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
--如果最大的值有多个:
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
)