查询成绩最好的前两名 |
您所在的位置：网站首页 › sql各科成绩最好的前十名 › 查询成绩最好的前两名 |
查询成绩最好的前两名
一、简单查询
二、汇总分析
-- 课程编号0002总成绩
SELECT SUM(`成绩`)
FROM score
WHERE `课程号` = '0002';
-- 选课人数
SELECT count(DISTINCT 学号) AS 选课人数
FROM score;
-- 各科成绩最高分与最低分
SELECT `课程号`,MAX(`成绩`) AS 最高分,MIN(`成绩`) AS 最低分
FROM score
GROUP BY `课程号`;
-- 每门课程选修人数
SELECT `课程号`,COUNT(*) as 选修人数
FROM score
GROUP BY `课程号`;
-- 查询男女人数
SELECT `性别`,COUNT(*) AS `人数`
FROM student
GROUP BY `性别`;
-- 查询平均成绩大于60分学生的学号和平均成绩
SELECT `学号`, AVG(`成绩`)
FROM score
GROUP BY `学号`
HAVING AVG(成绩) > 60;
-- 查询至少选修两门课程的学生学号
SELECT `学号`,COUNT(`课程号`) AS 选修课程数
FROM score
GROUP BY `学号`
HAVING COUNT(`课程号`) >= 2;
-- 查询同名同姓名单并统计人数
SELECT `姓名`, COUNT(*) AS 同名人数
FROM student
GROUP BY `姓名`
HAVING COUNT(*) > 1;
-- 查询及格的课程并按课程号从小到大排列
SELECT 课程号, `成绩`
FROM score
HAVING `成绩` >=60
ORDER BY `课程号` ASC;
-- 查询每门课程平均成绩，结果按平均成绩升序排序，平均成绩相同，按课程号降序
SELECT `课程号`, AVG(`成绩`) AS 平均成绩
FROM score
GROUP BY `课程号`
ORDER BY AVG(`成绩`) ASC , 课程号 DESC;
-- 检索课程编号‘0003’且分数大于60的学生学号，结果按分数降序排列
SELECT `学号`, `成绩`
FROM score
WHERE `课程号` = '0003' AND `成绩` > 60
ORDER BY `成绩` DESC;
-- 统计每门课程选修人数（超过两人才统计），输出课程号与选修人数，按人数降序，人数相同按课程号升序
SELECT `课程号`,COUNT(*)
FROM score
GROUP BY `课程号`
HAVING COUNT(*) > 2
ORDER BY COUNT(*) DESC ,`课程号` ASC;
-- 查询两门以上不及格课程的同学学号，以及不及格课程及平均成绩
SELECT `学号`,AVG(`成绩`) AS 平均成绩
FROM score
WHERE `成绩` < 60
GROUP BY `学号`
HAVING COUNT(`课程号`) > 1;
三、复杂查询
-- 1查询所有课程成绩小于60分的学生的学号、姓名
SELECT `学号`,`姓名`
FROM student
WHERE `学号` IN
(
SELECT `学号`
FROM score
GROUP BY `学号`
HAVING MAX(`成绩`) < 60
);
-- 2查询没有学全所有课的学生学号和姓名
SELECT `学号`,`姓名`
FROM student
WHERE `学号` IN
(
SELECT `学号`
FROM score
GROUP BY `学号`
HAVING COUNT(`课程号`) < (SELECT COUNT(`课程号`) FROM course)
);
-- 3、4查询只选了两门课程的学生学号和姓名
SELECT `学号`,`姓名`
FROM student
WHERE `学号` IN
(
SELECT `学号`
FROM score
GROUP BY `学号`
HAVING COUNT(`课程号`) =2
);
SELECT stu.`学号`,stu.`姓名`
FROM student stu INNER JOIN score sco
ON stu.`学号` = sco.`学号`
GROUP BY stu.`学号`
HAVING COUNT(sco.`课程号`) =2;
-- 5查找1990年出生的学生名单
SELECT `学号`, `姓名`
FROM student
WHERE YEAR(`出生日期`) = 1990;
-- 6按课程号分组取成绩最大值所在行数据
SELECT *
FROM score a
WHERE `成绩` =
(
SELECT MAX(成绩)
FROM score b
WHERE a.`课程号` = b.`课程号`
GROUP BY `课程号`
);
-- 7按课程号分组取成绩最小值所在行数据
SELECT *
FROM score a
WHERE `成绩` =
(
SELECT MIN(成绩)
FROM score b
WHERE a.`课程号` = b.`课程号`
GROUP BY `课程号`
);
-- 8查询各科成绩前两名信息
(SELECT * FROM score WHERE `课程号`= '0001' ORDER BY 成绩 DESC LIMIT 2)
UNION all
(SELECT * FROM score WHERE `课程号`= '0002' ORDER BY 成绩 DESC LIMIT 2)
UNION all
(SELECT * FROM score WHERE `课程号`= '0003' ORDER BY 成绩 DESC LIMIT 2);
四、多表查询
1.常规 -- 1查询所有学生的学号、姓名、选课数、总成绩 SELECT stu.`学号`,`姓名`,COUNT(`课程号`) AS 选课数,SUM(`成绩`) AS 总成绩 FROM student stu INNER JOIN score sco ON stu.`学号` = sco.`学号` GROUP BY stu.`学号`; -- 2查询平均成绩大于85的所有学生学号姓名平均成绩 SELECT stu.`学号`,stu.`姓名`,AVG(sco.`成绩`) AS 平均成绩 FROM student stu INNER JOIN score sco ON stu.`学号`= sco.`学号` GROUP BY stu.`学号` HAVING 平均成绩>85; -- 3查询所有学生学号、姓名、课程号、课程名称 SELECT stu.`学号`,stu.`姓名`,cou.`课程号`,cou.`课程名称` FROM student stu INNER JOIN score sco ON stu.`学号`= sco.`学号` INNER JOIN course cou ON sco.`课程号` = cou.`课程号`; -- 4查询出每门课程的及格与不及格人数 SELECT `课程号`, SUM(CASE WHEN `成绩`>= 60 THEN 1 ELSE 0 END) AS 及格人数, SUM(CASE WHEN `成绩`< 60 THEN 1 ELSE 0 END) AS 不及格人数 FROM score GROUP BY `课程号`; -- 5分段统计各科成绩[85-100][70-85][60-70][ 80;2.行列转换 
1.使用常量列表输出目标表结构 
2.使用case表达式，替换常量对应列成绩 
3.分组使用max函数取每个方块的最大值 
五、注意事项
1.SELECT语句尽量避免使用* 2.WHERE 子句比较符号左侧避免使用函数尽量避免在where条件子句中，比较符号的左侧出现表达式、函数等操作。因为这会导致数据库引擎进行全表扫描，从而增加运行时间。eg： where 成绩+5 > 90 --> where 成绩 > 90-5 3.尽量避免使用in和not in in和not in也会导致数据库进行全表搜索，增加运行时间。eg where 学号 in （8，9） --> where 学号 between 8 and 9 4.尽量避免使用oror同样会导致数据库进项全表搜索。在工作中，如果你只想用or从几十万语句中取几条出来，是非常划不来的，怎么办呢？下面的方法可替代or。eg.select 学号 from 成绩表 where 成绩 = 88 or 成绩 = 89 -->select 学号 from 成绩表 where 成绩 = 88 union select 学号 from 成绩表 where 成绩 = 89语句虽然变长了一点，但处理大量数据时，可以省下很多时间，是非常值得的。 5.使用limit子句限制返回的数据行数如果前台只需要显示15行数据，而你的查询结果集返回了1万行，那么这适合最好使用limt子句来限制查询返回的数据行数。 |
【本文地址】
公司简介
联系我们
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |