请教一个sql语句问题

[sysabod]

大家好，我的代码如下：

o --q5
;with T(s_id,avg_score) as
(select X.s_id ,avg(score)
		from score,
				(select s_id  from score where score >= 80
						 except
				select s_id from score where score < 80)
			    as X 
	where X.s_id=score.s_id
	group by X.s_id	

)
select top 3 * from T order by avg_acore desc

消息 207，级别 16，状态 1，第 13 行
列名 'avg_acore' 无效
为什么会这样呢？我的意思是先把试图T按avg_score按大到小排列。再取头三列，请问应该怎么正确实现呢？

[--小F--]

---try
;with T(s_id,avg_score) as
(select X.s_id ,avg(score) as avg_score
        from score,
                (select s_id  from score where score >= 80
                         except
                select s_id from score where score < 80)
                as X 
    where X.s_id=score.s_id
    group by X.s_id    

)
select top 3 * from T order by avg_acore desc

[dawugui]

;with T(s_id,avg_score) as
(select X.s_id ,avg(score) avg_acore 
        from score,
                (select s_id  from score where score >= 80
                         except
                select s_id from score where score < 80)
                as X 
    where X.s_id=score.s_id
    group by X.s_id    

)
select top 3 * from T order by avg_acore desc

[bancxc]

不知道能不能你那样写
;with T  as
(select X.s_id ,avg_acore=avg(score)
        from score,
                (select s_id  from score where score >= 80
                         except
                select s_id from score where score < 80)
                as X 
    where X.s_id=score.s_id
    group by X.s_id    

)
select top 3 * from T order by avg_acore desc

[icelovey]

;WITH T  AS
(SELECT X.S_ID ,AVG_ACORE=AVG(SCORE)
        FROM SCORE,
                (SELECT S_ID  FROM SCORE WHERE SCORE >= 80
                         EXCEPT
                SELECT S_ID FROM SCORE WHERE SCORE < 80)
                AS X 
    WHERE X.S_ID=SCORE.S_ID
    GROUP BY X.S_ID    

)
SELECT TOP 3 * FROM T ORDER BY AVG_ACORE DESC

最好把给点例子数据出来， 这样大家就能调试对不对啦。。