一、取出各科成绩前三的学生

  1. #取出各科成绩前三的学生
  3. CREATE TABLE stu_score(
  5.     id INT PRIMARY KEY AUTO_INCREMENT,
  6.     stu_id INT,
  7.     stu_name VARCHAR(50),
  8.     clazz VARCHAR(20),
  9.     score DOUBLE,
  10.     remark VARCHAR(50)
  11. );
  14. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (1, '小赵', '语文', 80);
  15. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (1, '小赵', '数学', 85);
  16. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (1, '小赵', '英语', 90);
  17. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (1, '小赵', '化学', 90);
  19. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (2, '小钱', '语文', 88);
  20. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (2, '小钱', '数学', 88);
  21. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (2, '小钱', '英语', 98);
  22. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (2, '小钱', '化学', 92);
  24. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (3, '小孙', '语文', 70);
  25. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (3, '小孙', '数学', 78);
  26. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (3, '小孙', '英语', 96);
  27. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (3, '小孙', '化学', 95);
  29. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (4, '小李', '语文', 85);
  30. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (4, '小李', '数学', 98);
  31. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (4, '小李', '英语', 88);
  32. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (4, '小李', '化学', 98);
  34. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (5, '小王', '语文', 90);
  35. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (5, '小王', '数学', 80);
  36. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (5, '小王', '英语', 85);
  37. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (5, '小王', '化学', 78);
  39. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (6, '小陈', '语文', 87);
  40. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (6, '小陈', '数学', 97);
  41. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (6, '小陈', '英语', 84);
  42. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (6, '小陈', '化学', 80);
  44. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (7, '小朱', '语文', 80);
  45. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (7, '小朱', '数学', 88);#和小钱88分一样，并列第三
  46. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (7, '小朱', '英语', 98);#并列第一
  47. INSERT INTO stu_score(stu_id,stu_name,clazz,score) VALUES (7, '小朱', '化学', 95);#并列批二
  50. SELECT * FROM stu_score ss ORDER BY ss.`clazz`,ss.`score` DESC;
  53. #方法二
  54. #使用加行号的方式查询
  55. #加行号的使用规则是：@rowNum:=num
  56. #意思是声明一个叫 rowNum 的变量并赋值为num
  57. #此处加行号m1的作用就可以体现出来，查询语句中多了一个字段 r ，他可以以数字1,2,3,4,5的形式显示排名
  58. select score, stu_name, @m1:=@m1+1 r from stu_score, (select @m1:=0) a 
  59. where subject='java' 
  60. order by score desc 
  61. limit 3

方法一（不考虑并列）

查询速度快且代码简单

  1. #取出各科成绩前三的学生
  2. #1、取出一名学生A某个学科的成绩
  3. #2、然后和所有的学生同一个学科的成绩比较
  4. #3、当存在该学科的成绩比学生A的成绩大且总人数不大于3，或者该学科成绩没有大于学生A的，则表示学生A的成绩在前三
  5. #4、然后根据学科、分数（降序）排序取出
  6. SELECT * FROM stu_score ss1 
  7. WHERE (
  8.   SELECT COUNT(*) 
  9.   FROM stu_score ss2 
  10.   WHERE ss2.`clazz`=ss1.`clazz` AND ss2.`score` > ss1.`score`
  11. ) < 3 
  12. ORDER BY ss1.`clazz`,ss1.`score` DESC;

最后的查询结果：

  1. id      stu_id  stu_name  clazz      score    remark  
  2. ------  ------  --------  ------     ------   --------
  3.     16       4  小李        化学          98  (NULL)  
  4.     12       3  小孙        化学          95  (NULL)  
  5.     28       7  小朱        化学          95  (NULL)  
  6.     14       4  小李        数学          98  (NULL)  
  7.     22       6  小陈        数学          97  (NULL)  
  8.      6       2  小钱        数学          88  (NULL)  
  9.      7       2  小钱        英语          98  (NULL)  
  10.     27       7  小朱        英语          98  (NULL)  
  11.     11       3  小孙        英语          96  (NULL)  
  12.     17       5  小王        语文          90  (NULL)  
  13.      5       2  小钱        语文          88  (NULL)  
  14.     21       6  小陈        语文          87  (NULL)

方法二（考虑并列）

  1. #并列的情况
  2. SELECT s1.stu_name, s1.clazz, s1.score
  3. FROM stu_score s1
  4.     LEFT JOIN (
  5.         SELECT DISTINCT clazz, score
  6.         FROM stu_score
  7.     ) s2
  8.     ON s1.clazz = s2.clazz
  9.         AND s1.score < s2.score
  10. GROUP BY s1.stu_name, s1.clazz, s1.score
  11. HAVING COUNT(1) < 3
  12. ORDER BY clazz, score DESC;

最后的查询结果： 小钱-化学，小朱-数学

  1. stu_id  stu_name  clazz      score    remark  
  2. ------  --------  ------     ------   --------
  3. 4  小李        化学          98  (NULL)  
  4. 3  小孙        化学          95  (NULL)  
  5. 7  小朱        化学          95  (NULL) 
  6. 2  小钱        化学          92  (NULL) 
  7. 4  小李        数学          98  (NULL)  
  8. 6  小陈        数学          97  (NULL)  
  9. 2  小钱        数学          88  (NULL)  
  10. 7  小朱        数学          88  (NULL)  
  11. 2  小钱        英语          98  (NULL)  
  12. 7  小朱        英语          98  (NULL)  
  13. 3  小孙        英语          96  (NULL)  
  14. 5  小王        语文          90  (NULL)  
  15. 2  小钱        语文          88  (NULL)  
  16. 6  小陈        语文          87  (NULL)

二、取出各科成绩都不小于85分的学生

  1. #取出各科成绩都不小于85分的学生
  2. #如果一个学生所有学科的分数中，最小的都>=85，则表示该学生每科成绩都不小于85
  3. SELECT stu_name,MIN(score) FROM stu_score  
  4. GROUP BY stu_name HAVING MIN(score) >=85 
  5. ORDER BY MIN(score) DESC;

最后的查询结果：

  1. stu_name  MIN(score)  
  2. --------  ------------
  3. 小钱                88
  4. 小李                85