from后面给表起了别名，但是后面的where中用不了

Question

select Student.Sname, t2.SId, t2.avgsc
from 
    Student,
    (select t1.SId SId,avg(t1.score) avgsc from (select SId, score  from SC where score < 60) t1 group by t1.SId)  t2
where 
    Student.SId in (
        select SId from 
        (select t1.SId SId, avg(t1.score) avgsc from (select SId,  score from SC where score < 60) t1 group by t1.SId) t2
    );

 t2在

Student.SId in (select SId from (select t1.SId SId,avg(t1.score) avgsc
from
    (select SId,score from SC where score < 60) t1  group by t1.SId) t2);

 这一段不能直接使用中间表别名

where Student.SId in (select SId from t2)  会报错

 

Answer 1

你这个t2是子查询内部的别名，这样不行的。放在上面的字段不可以是子查询的别名。