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)
*/
