select查询 带Group by 后面想用AS怎么写？

Question

大家好，我现在有个问题，想请各位高手帮一下忙 select distinct EvaluateTeach.TutorshipID, tutorship.teacherusername , EvaluateTeach.TeachTaskNo as teachtaskno,avg(evaluateteach.grade) as finalscore from vteachtask ,vteacher,term,tutorship,evaluateteach where vteachtask.years=term.years and vteachtask.termturn =term.termturn and term.iscurrentterm=1 and tutorship.teacherusername=vteacher.username and evaluateteach.teachtaskno=vteachtask.no and EvaluateTeach.TutorshipID=Tutorship.ID group by EvaluateTeach.TutorshipID,tutorship.teacherusername,EvaluateTeach.TeachTaskNo 我想在上述语句的后面加一个AS B 让select的结果成为表B

Answer 1

Select * From
(
select distinct EvaluateTeach.TutorshipID, tutorship.teacherusername , EvaluateTeach.TeachTaskNo as teachtaskno,avg(evaluateteach.grade) as finalscore from vteachtask ,vteacher,term,tutorship,evaluateteach where vteachtask.years=term.years and vteachtask.termturn =term.termturn and term.iscurrentterm=1 and tutorship.teacherusername=vteacher.username and evaluateteach.teachtaskno=vteachtask.no and EvaluateTeach.TutorshipID=Tutorship.ID group by EvaluateTeach.TutorshipID,tutorship.teacherusername,EvaluateTeach.TeachTaskNo
) B

你是这个意思吗？

Answer 2

大家好，我现在有个问题，想请各位高手帮一下忙 select distinct EvaluateTeach.TutorshipID, tutorship.teacherusername , EvaluateTeach.TeachTaskNo as teachtaskno,avg(evaluateteach.grade) as finalscore from vteachtask ,vteacher,term,tutorship,evaluateteach where vteachtask.years=term.years and vteachtask.termturn =term.termturn and term.iscurrentterm=1 and tutorship.teacherusername=vteacher.username and evaluateteach.teachtaskno=vteachtask.no and EvaluateTeach.TutorshipID=Tutorship.ID group by EvaluateTeach.TutorshipID,tutorship.teacherusername,EvaluateTeach.TeachTaskNo 我想在上述语句的后面加一个AS B 让select的结果成为表B

select ... into B from ...
可以存成一个表。
如果只是显示，可以用视图呀。

---

学习，学习，再学习

Answer 3

恩，就是这个意思

谢谢

Answer 4

谢谢

Answer 5

还可以with。比如

with B as (

select distinct EvaluateTeach.TutorshipID, tutorship.teacherusername , EvaluateTeach.TeachTaskNo as teachtaskno,avg(evaluateteach.grade) as finalscore from vteachtask ,vteacher,term,tutorship,evaluateteach where vteachtask.years=term.years and vteachtask.termturn =term.termturn and term.iscurrentterm=1 and tutorship.teacherusername=vteacher.username and evaluateteach.teachtaskno=vteachtask.no and EvaluateTeach.TutorshipID=Tutorship.ID group by EvaluateTeach.TutorshipID,tutorship.teacherusername,EvaluateTeach.TeachTaskNo)

select *

from B --调用它

Answer 6

你的这个WITH 的用法我尝试了一下，老是报在关键字WITH附近有语法错误
我用的是05 ，

语句和你的一样 
刚才我看了一下服务器上用的是2000 的数据库，2000里面不支持WITH语法

Answer 7

2000没有CTE