none
select查询 带Group by 后面想用AS怎么写? RRS feed

  • 问题

  • 大家好,我现在有个问题,想请各位高手帮一下忙 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
    2009年12月28日 14:20

答案

  • 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

    你是这个意思吗?
    • 已标记为答案 LTL007 2009年12月29日 3:01
    2009年12月29日 0:54
  • 大家好,我现在有个问题,想请各位高手帮一下忙 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 ...
    可以存成一个表。
    如果只是显示,可以用视图呀。
    学习,学习,再学习
    • 已标记为答案 LTL007 2009年12月29日 3:01
    2009年12月29日 2:56

全部回复

  • 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

    你是这个意思吗?
    • 已标记为答案 LTL007 2009年12月29日 3:01
    2009年12月29日 0:54
  • 大家好,我现在有个问题,想请各位高手帮一下忙 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 ...
    可以存成一个表。
    如果只是显示,可以用视图呀。
    学习,学习,再学习
    • 已标记为答案 LTL007 2009年12月29日 3:01
    2009年12月29日 2:56
  • 恩,就是这个意思

    谢谢

    2009年12月29日 3:00
  • 谢谢

    2009年12月29日 3:00
  • 还可以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 --调用它
    2009年12月29日 5:21
  • 你的这个WITH 的用法我尝试了一下,老是报在关键字WITH附近有语法错误
    我用的是05 ,

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

    2009年12月30日 1:26
  • 2000没有CTE
    2009年12月30日 6:09