大家好,我的代码如下: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按大到小排列。再取头三列,请问应该怎么正确实现呢?
;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按大到小排列。再取头三列,请问应该怎么正确实现呢?
;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
(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
;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
;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最好把给点例子数据出来, 这样大家就能调试对不对啦。。