一个简单的学生总成绩第二的 sql查询
CREATE TABLE stucores(
id INT,/*学生id*/
NAME VARCHAR(11),/*学生姓名*/
course VARCHAR(11),/*课程名称*/
score INT,/*单科分数*/
PRIMARY KEY(id,course)
)
INSERT INTO stucores(id,NAME,course,score) VALUES(1,'张三','语文',23);
INSERT INTO stucores(id,NAME,course,score) VALUES(1,'张三','数学',25);
INSERT INTO stucores(id,NAME,course,score) VALUES(2,'李四','语文',43);
INSERT INTO stucores(id,NAME,course,score) VALUES(2,'李四','数学',67);
INSERT INTO stucores(id,NAME,course,score) VALUES(3,'王五','语文',89);
INSERT INTO stucores(id,NAME,course,score) VALUES(3,'王五','数学',28);
INSERT INTO stucores(id,NAME,course,score) VALUES(4,'赵六','语文',35);
INSERT INTO stucores(id,NAME,course,score) VALUES(4,'赵六','数学',78);
INSERT INTO stucores(id,NAME,course,score) VALUES(5,'钱七','语文',89);
INSERT INTO stucores(id,NAME,course,score) VALUES(5,'钱七','数学',97);
SELECT id,NAME,SUM(score) '总成绩' FROM stucores GROUP BY id;#分组求每组的总成绩
SELECT id,NAME,SUM(score) '总成绩' FROM stucores GROUP BY id ORDER BY '总成绩' DESC;#这样排序无效
SELECT id,NAME,SUM(score) '总成绩' FROM stucores GROUP BY id ORDER BY '总成绩' DESC LIMIT 1,1;#取出来的第二高也是错的
SELECT t.id,t.name,t.s '总成绩' FROM
(SELECT id,NAME,SUM(score) s FROM stucores GROUP BY id) t
ORDER BY t.s DESC;#对分组后的数据重新select 然后排序才有效
SELECT t.id,t.name,t.s '总成绩' FROM
(SELECT id,NAME,SUM(score) s FROM stucores GROUP BY id) t
ORDER BY t.s DESC LIMIT 1,1;#对排序后的结果取第二高
