皮皮网

1.�༶htmlԴ��

�༶htmlԴ��

       CREATE TABLE #student

       (id INT ,班级班级classid INT,grade INT)

       INSERT INTO #student VALUES(1,1,)

       INSERT INTO #student VALUES(2,1,)

       INSERT INTO #student VALUES(3,1,)

       INSERT INTO #student VALUES(4,2,)

       INSERT INTO #student VALUES(5,2,)

       INSERT INTO #student VALUES(6,2,)

       INSERT INTO #student VALUES(7,3,)

       INSERT INTO #student VALUES(8,3,)

       INSERT INTO #student VALUES(9,3,)

       SELECT id AS '学号',classid AS '班级',grade as '分数' FROM #student s

       --请问计算各个班平均成绩的SQL语句是？

       SELECT classid,AVG(grade) AS '平均成绩' FROM #student GROUP BY classid

       /

       classid 平均成绩

       ----------- -----------

       1

       2

       3

       (3 row(s) affected)

       */

       --还有查找比该班平均成绩高的学生班级classid，学号id，源码源代间谍指标公式源码成绩grade的网页兵临城下源码语句

       SELECT

        s.id,

        s.classid,

        s.grade

       FROM

        #student s ,(SELECT classid,AVG(grade) AS 'avgGrade' FROM #student GROUP BY classid) s2

       WHERE

       s.classid = s2.classid AND s.grade>s2.avgGrade

       /

       结果.想了半天,用一条语句好像很难实现..就嵌套了一个子查询.

       id classid grade

       ----------- ----------- -----------

       1 1

       4 2

       7 3

       8 3

       (4 row(s) affected)

       */