{"id":2743,"date":"2024-12-17T09:08:36","date_gmt":"2024-12-17T01:08:36","guid":{"rendered":"https:\/\/usei.cn\/?p=2743"},"modified":"2024-12-17T10:14:12","modified_gmt":"2024-12-17T02:14:12","slug":"sql-%e5%88%86%e9%98%b6%e6%ae%b5%e7%bb%83%e4%b9%a0%e9%a2%98","status":"publish","type":"post","link":"https:\/\/usei.cn\/index.php\/2024\/12\/17\/sql-%e5%88%86%e9%98%b6%e6%ae%b5%e7%bb%83%e4%b9%a0%e9%a2%98\/","title":{"rendered":"SQL \u5206\u9636\u6bb5\u7ec3\u4e60\u9898-\u7b54\u6848"},"content":{"rendered":"\n

\u4e8c.\u7b54\u6848<\/h2>\n\n\n\n

1. \u67e5\u8be2\u6240\u6709\u5b66\u751f\u7684\u4fe1\u606f\u3002<\/h3>\n\n\n\n
SELECT * FROM students;
<\/code><\/pre>\n\n\n\n
2. \u67e5\u8be2\u6240\u6709\u8001\u5e08\u7684\u4fe1\u606f\u3002<\/h3>\n\n\n\n
SELECT * FROM teachers;
<\/code><\/pre>\n\n\n\n
3. \u67e5\u8be2\u6240\u6709\u8bfe\u7a0b\u7684\u4fe1\u606f\u3002<\/h3>\n\n\n\n
SELECT * FROM courses;
<\/code><\/pre>\n\n\n\n
4. \u67e5\u8be2\u6240\u6709\u5b66\u751f\u7684\u59d3\u540d\u548c\u5e74\u9f84\u3002<\/h4>\n\n\n\n
SELECT name, age FROM students;
<\/code><\/pre>\n\n\n\n
5. \u67e5\u8be2\u6240\u6709\u9009\u4fee\u201c\u6570\u636e\u7ed3\u6784\u201d\u8bfe\u7a0b\u7684\u5b66\u751f\u59d3\u540d\u3002<\/h4>\n\n\n\n
SELECT s.name 
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE c.course_name = '\u6570\u636e\u7ed3\u6784';
<\/code><\/pre>\n\n\n\n
6. \u67e5\u8be2\u9009\u4fee\u4e86\u201c\u9ad8\u7b49\u6570\u5b66\u201d\u8bfe\u7a0b\u7684\u5b66\u751f\u59d3\u540d\u548c\u6210\u7ee9\u3002<\/h3>\n\n\n\n
SELECT s.name, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE c.course_name = '\u9ad8\u7b49\u6570\u5b66';
<\/code><\/pre>\n\n\n\n
7. \u67e5\u8be2\u6240\u6709\u8bfe\u7a0b\u7684\u8bfe\u7a0b\u540d\u79f0\u548c\u5bf9\u5e94\u7684\u8001\u5e08\u59d3\u540d\u3002<\/h3>\n\n\n\n
SELECT c.course_name, t.name
FROM courses c
JOIN teachers t ON c.teacher_id = t.teacher_id;
<\/code><\/pre>\n\n\n\n
8. \u67e5\u8be2\u9009\u4fee\u4e86\u201c\u91cf\u5b50\u529b\u5b66\u201d\u8bfe\u7a0b\u7684\u5b66\u751f\u59d3\u540d\u548c\u6210\u7ee9\uff0c\u5e76\u6309\u7167\u6210\u7ee9\u4ece\u9ad8\u5230\u4f4e\u6392\u5e8f\u3002<\/h3>\n\n\n\n
SELECT s.name, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE c.course_name = '\u91cf\u5b50\u529b\u5b66'
ORDER BY sc.grade DESC;
<\/code><\/pre>\n\n\n\n
9. \u67e5\u8be2\u6bcf\u4f4d\u5b66\u751f\u7684\u8bfe\u7a0b\u540d\u79f0\u548c\u6210\u7ee9\u3002<\/h3>\n\n\n\n
SELECT s.name, c.course_name, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id;
<\/code><\/pre>\n\n\n\n
10. \u67e5\u8be2\u6240\u6709\u5b66\u751f\u7684\u5e73\u5747\u6210\u7ee9\uff08\u6309\u5b66\u751f\u5206\u7ec4\uff09\u3002<\/h3>\n\n\n\n
SELECT s.name, AVG(sc.grade) AS avg_grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id;
<\/code><\/pre>\n\n\n\n
11. \u67e5\u8be2\u6bcf\u95e8\u8bfe\u7a0b\u7684\u5e73\u5747\u6210\u7ee9\uff08\u6309\u8bfe\u7a0b\u5206\u7ec4\uff09\u3002<\/h3>\n\n\n\n
SELECT c.course_name, AVG(sc.grade) AS avg_grade
FROM courses c
JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY c.course_id;
<\/code><\/pre>\n\n\n\n
12. \u67e5\u8be2\u6210\u7ee9\u5927\u4e8e80\u7684\u5b66\u751f\u59d3\u540d\u548c\u6210\u7ee9\u3002<\/h3>\n\n\n\n
SELECT s.name, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
WHERE sc.grade > 80;
<\/code><\/pre>\n\n\n\n
13. \u67e5\u8be2\u6bcf\u4f4d\u8001\u5e08\u6240\u6559\u6388\u7684\u8bfe\u7a0b\u6570\u91cf\u3002<\/h3>\n\n\n\n
SELECT t.name, COUNT(c.course_id) AS course_count
FROM teachers t
LEFT JOIN courses c ON t.teacher_id = c.teacher_id
GROUP BY t.teacher_id;
<\/code><\/pre>\n\n\n\n
14. \u67e5\u8be2\u6240\u6709\u8bfe\u7a0b\u4ee5\u53ca\u6ca1\u6709\u5b66\u751f\u9009\u4fee\u7684\u8bfe\u7a0b\uff08\u5373\u6ca1\u6709\u51fa\u73b0\u5728\u5b66\u751f\u9009\u8bfe\u8868\u4e2d\u7684\u8bfe\u7a0b\uff09\u3002<\/h3>\n\n\n\n
SELECT c.course_name
FROM courses c
LEFT JOIN student_courses sc ON c.course_id = sc.course_id
WHERE sc.student_id IS NULL;
<\/code><\/pre>\n\n\n\n
15. \u67e5\u8be2\u6240\u6709\u9009\u4fee\u4e86\u201c\u6709\u673a\u5316\u5b66\u201d\u8bfe\u7a0b\u7684\u5b66\u751f\u6570\u91cf\u3002<\/h3>\n\n\n\n
SELECT COUNT(DISTINCT sc.student_id) AS student_count
FROM student_courses sc
JOIN courses c ON sc.course_id = c.course_id
WHERE c.course_name = '\u6709\u673a\u5316\u5b66';
<\/code><\/pre>\n\n\n\n
16. \u67e5\u8be2\u6240\u6709\u5b66\u751f\u7684\u59d3\u540d\u53ca\u5176\u6240\u9009\u7684\u8bfe\u7a0b\u540d\u79f0\uff0c\u8981\u6c42\u6bcf\u4e2a\u5b66\u751f\u81f3\u5c11\u9009\u4e00\u95e8\u8bfe\u7a0b\u3002<\/h3>\n\n\n\n
SELECT s.name, c.course_name
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id;
<\/code><\/pre>\n\n\n\n
17. \u67e5\u8be2\u6bcf\u95e8\u8bfe\u7a0b\u7684\u6700\u9ad8\u5206\u548c\u6700\u4f4e\u5206\u3002<\/h3>\n\n\n\n
SELECT c.course_name, MAX(sc.grade) AS max_grade, MIN(sc.grade) AS min_grade
FROM courses c
JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY c.course_id;
<\/code><\/pre>\n\n\n\n
18. \u67e5\u8be2\u9009\u4fee\u4e86\u6700\u591a\u8bfe\u7a0b\u7684\u5b66\u751f\u59d3\u540d\u3002<\/h3>\n\n\n\n
SELECT s.name
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id
ORDER BY COUNT(sc.course_id) DESC
LIMIT 1;
<\/code><\/pre>\n\n\n\n
19. \u67e5\u8be2\u6bcf\u4f4d\u8001\u5e08\u6559\u6388\u7684\u8bfe\u7a0b\u53ca\u5176\u5b66\u751f\u7684\u5e73\u5747\u6210\u7ee9\uff08\u6309\u8001\u5e08\u5206\u7ec4\uff09\u3002<\/h3>\n\n\n\n
SELECT t.name AS teacher_name, c.course_name, AVG(sc.grade) AS avg_grade
FROM teachers t
JOIN courses c ON t.teacher_id = c.teacher_id
JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY t.teacher_id, c.course_id;
<\/code><\/pre>\n\n\n\n
20. \u67e5\u8be2\u5e74\u9f84\u5927\u4e8e20\u5c81\u7684\u5b66\u751f\u6240\u9009\u7684\u8bfe\u7a0b\u540d\u79f0\u53ca\u5176\u6210\u7ee9\u3002<\/h3>\n\n\n\n
SELECT s.name, c.course_name, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE s.age > 20;
<\/code><\/pre>\n\n\n\n
21. \u67e5\u8be2\u6240\u6709\u5b66\u751f\u7684\u8bfe\u7a0b\u540d\u79f0\u548c\u6210\u7ee9\uff0c\u8981\u6c42\u6bcf\u4f4d\u5b66\u751f\u6bcf\u95e8\u8bfe\u7a0b\u7684\u6210\u7ee9\u663e\u793a\u5728\u540c\u4e00\u884c\u3002<\/h3>\n\n\n\n
SELECT s.name,
       MAX(CASE WHEN c.course_name = '\u6570\u636e\u7ed3\u6784' THEN sc.grade END) AS \u6570\u636e\u7ed3\u6784,
       MAX(CASE WHEN c.course_name = '\u9ad8\u7b49\u6570\u5b66' THEN sc.grade END) AS \u9ad8\u7b49\u6570\u5b66,
       MAX(CASE WHEN c.course_name = '\u91cf\u5b50\u529b\u5b66' THEN sc.grade END) AS \u91cf\u5b50\u529b\u5b66,
       MAX(CASE WHEN c.course_name = '\u6709\u673a\u5316\u5b66' THEN sc.grade END) AS \u6709\u673a\u5316\u5b66
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
GROUP BY s.student_id;
<\/code><\/pre>\n\n\n\n
22. \u67e5\u8be2\u6bcf\u4e2a\u5b66\u751f\u7684\u8bfe\u7a0b\u6570\u91cf\u4ee5\u53ca\u662f\u5426\u9009\u4fee\u4e86\u201c\u91cf\u5b50\u529b\u5b66\u201d\u8bfe\u7a0b\u3002<\/h3>\n\n\n\n
SELECT s.name,
       COUNT(sc.course_id) AS course_count,
       MAX(CASE WHEN c.course_name = '\u91cf\u5b50\u529b\u5b66' THEN '\u662f' ELSE '\u5426' END) AS \u662f\u5426\u9009\u4fee\u91cf\u5b50\u529b\u5b66
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
GROUP BY s.student_id;
<\/code><\/pre>\n\n\n\n
23. \u67e5\u8be2\u6210\u7ee9\u572885\u4ee5\u4e0a\u7684\u5b66\u751f\uff0c\u6309\u8bfe\u7a0b\u540d\u79f0\u6392\u5e8f\uff0c\u663e\u793a\u5b66\u751f\u59d3\u540d\u548c\u8bfe\u7a0b\u540d\u79f0\u3002<\/h3>\n\n\n\n
SELECT s.name, c.course_name, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE sc.grade >= 85
ORDER BY c.course_name;
<\/code><\/pre>\n\n\n\n
24. \u67e5\u8be2\u6bcf\u95e8\u8bfe\u7a0b\u7684\u5e73\u5747\u6210\u7ee9\uff0c\u5e76\u663e\u793a\u6210\u7ee9\u4f4e\u4e8e\u5e73\u5747\u6210\u7ee9\u7684\u5b66\u751f\u3002<\/h3>\n\n\n\n
SELECT s.name, c.course_name, sc.grade, avg_grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
JOIN (
    SELECT course_id, AVG(grade) AS avg_grade
    FROM student_courses
    GROUP BY course_id
) AS avg_scores ON c.course_id = avg_scores.course_id
WHERE sc.grade < avg_scores.avg_grade;
<\/code><\/pre>\n\n\n\n
25. \u67e5\u8be2\u6bcf\u4e2a\u5b66\u751f\u5728\u4e0d\u540c\u8bfe\u7a0b\u4e2d\u7684\u6392\u540d\uff08\u6309\u6210\u7ee9\u4ece\u9ad8\u5230\u4f4e\u6392\u540d\uff09\uff0c\u53ea\u663e\u793a\u524d3\u540d\u7684\u5b66\u751f\u3002<\/h3>\n\n\n\n
SELECT s.name, c.course_name, sc.grade,
       RANK() OVER (PARTITION BY c.course_name ORDER BY sc.grade DESC) AS rank
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE RANK() OVER (PARTITION BY c.course_name ORDER BY sc.grade DESC) <= 3;
<\/code><\/pre>\n\n\n\n
26. \u67e5\u8be2\u6bcf\u4f4d\u8001\u5e08\u6559\u6388\u7684\u6240\u6709\u8bfe\u7a0b\u53ca\u5176\u5b66\u751f\u7684\u5e73\u5747\u6210\u7ee9\uff0c\u5e76\u4e14\u6309\u7167\u6559\u5e08\u7684\u5e73\u5747\u6210\u7ee9\u6392\u5e8f\u3002<\/h3>\n\n\n\n
SELECT t.name AS teacher_name, c.course_name, AVG(sc.grade) AS avg_grade
FROM teachers t
JOIN courses c ON t.teacher_id = c.teacher_id
JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY t.teacher_id, c.course_id
ORDER BY AVG(sc.grade) DESC;
<\/code><\/pre>\n\n\n\n
27. \u67e5\u8be2\u9009\u4fee\u6700\u591a\u8bfe\u7a0b\u7684\u5b66\u751f\u548c\u6700\u5c11\u8bfe\u7a0b\u7684\u5b66\u751f\uff0c\u5e76\u5206\u522b\u663e\u793a\u5176\u6240\u9009\u7684\u8bfe\u7a0b\u6570\u91cf\u3002<\/h3>\n\n\n\n
SELECT s.name, COUNT(sc.course_id) AS course_count
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id
ORDER BY course_count DESC
LIMIT 1;

SELECT s.name, COUNT(sc.course_id) AS course_count
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id
ORDER BY course_count ASC
LIMIT 1;
<\/code><\/pre>\n\n\n\n
28. \u67e5\u8be2\u6bcf\u4f4d\u5b66\u751f\u53ca\u5176\u9009\u4fee\u8bfe\u7a0b\u7684\u540d\u79f0\u548c\u6210\u7ee9\uff0c\u53ea\u663e\u793a\u6709\u6210\u7ee9\u5927\u4e8e80\u7684\u8bfe\u7a0b\u3002<\/h3>\n\n\n\n
SELECT s.name, c.course_name, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE sc.grade > 80;
<\/code><\/pre>\n\n\n\n
29. \u67e5\u8be2\u6bcf\u4f4d\u5b66\u751f\u7684\u8bfe\u7a0b\u6570\u91cf\uff0c\u53ea\u6709\u9009\u4fee\u8bfe\u7a0b\u6570\u91cf\u5927\u4e8e\u7b49\u4e8e2\u95e8\u7684\u5b66\u751f\u624d\u663e\u793a\u3002<\/h3>\n\n\n\n
SELECT s.name, COUNT(sc.course_id) AS course_count
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id
HAVING COUNT(sc.course_id) >= 2;
<\/code><\/pre>\n\n\n\n
30. \u67e5\u8be2\u6bcf\u95e8\u8bfe\u7a0b\u7684\u5b66\u751f\u6570\u91cf\uff0c\u5e76\u663e\u793a\u5b66\u751f\u4eba\u6570\u8d85\u8fc72\u4eba\u7684\u8bfe\u7a0b\u3002<\/h3>\n\n\n\n
SELECT c.course_name, COUNT(sc.student_id) AS student_count
FROM courses c
JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY c.course_id
HAVING COUNT(sc.student_id) > 2;
<\/code><\/pre>\n\n\n\n
31. \u67e5\u8be2\u6240\u6709\u6ca1\u6709\u9009\u4fee\u4efb\u4f55\u8bfe\u7a0b\u7684\u5b66\u751f\u59d3\u540d\u3002<\/h3>\n\n\n\n
SELECT s.name
FROM students s
LEFT JOIN student_courses sc ON s.student_id = sc.student_id
WHERE sc.course_id IS NULL;
<\/code><\/pre>\n\n\n\n
32. \u67e5\u8be2\u6bcf\u4e2a\u5b66\u751f\u7684\u6392\u540d\uff08\u6309\u6210\u7ee9\u603b\u548c\u964d\u5e8f\u6392\u5217\uff09\uff0c\u5e76\u663e\u793a\u5b66\u751f\u7684\u603b\u6210\u7ee9\u3002<\/h3>\n\n\n\n
SELECT s.name, SUM(sc.grade) AS total_score,
       RANK() OVER (ORDER BY SUM(sc.grade) DESC) AS rank
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id;
<\/code><\/pre>\n\n\n\n
33. \u67e5\u8be2\u6bcf\u4f4d\u5b66\u751f\u9009\u4fee\u7684\u8bfe\u7a0b\u540d\u79f0\u548c\u6210\u7ee9\uff0c\u5e76\u6309\u7167\u5b66\u751f\u7684\u603b\u6210\u7ee9\u4ece\u9ad8\u5230\u4f4e\u6392\u5e8f\u3002<\/h3>\n\n\n\n
SELECT s.name, c.course_name, sc.grade, SUM(sc.grade) OVER (PARTITION BY s.student_id) AS total_score
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
ORDER BY total_score DESC;
<\/code><\/pre>\n\n\n\n
34. \u67e5\u8be2\u6bcf\u4f4d\u8001\u5e08\u6559\u6388\u7684\u6240\u6709\u8bfe\u7a0b\u7684\u5b66\u751f\u4eba\u6570\uff0c\u6309\u5b66\u751f\u4eba\u6570\u964d\u5e8f\u6392\u5217\u3002<\/h3>\n\n\n\n
SELECT t.name AS teacher_name, c.course_name, COUNT(sc.student_id) AS student_count
FROM teachers t
JOIN courses c ON t.teacher_id = c.teacher_id
JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY t.teacher_id, c.course_id
ORDER BY student_count DESC;
<\/code><\/pre>\n\n\n\n
35. \u67e5\u8be2\u8bfe\u7a0b\u201c\u6570\u636e\u7ed3\u6784\u201d\u4e2d\uff0c\u6210\u7ee9\u5927\u4e8e90\u7684\u5b66\u751f\u7684\u59d3\u540d\u548c\u6210\u7ee9\u3002<\/h3>\n\n\n\n
SELECT s.name, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE c.course_name = '\u6570\u636e\u7ed3\u6784' AND sc.grade > 90;
<\/code><\/pre>\n\n\n\n
36. \u67e5\u8be2\u9009\u4fee\u4e86\u201c\u91cf\u5b50\u529b\u5b66\u201d\u8bfe\u7a0b\u7684\u5b66\u751f\uff0c\u4e14\u4ed6\u4eec\u7684\u603b\u6210\u7ee9\u8d85\u8fc7300\u7684\u5b66\u751f\u59d3\u540d\u3002<\/h3>\n\n\n\n
SELECT s.name
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
GROUP BY s.student_id
HAVING SUM(sc.grade) > 300 AND MAX(c.course_name) = '\u91cf\u5b50\u529b\u5b66';
<\/code><\/pre>\n\n\n\n
37. \u67e5\u8be2\u6bcf\u4e2a\u5b66\u751f\u7684\u6392\u540d\uff08\u6309\u603b\u6210\u7ee9\u964d\u5e8f\u6392\u5217\uff09\uff0c\u663e\u793a\u6392\u540d\u3001\u5b66\u751f\u59d3\u540d\u548c\u603b\u6210\u7ee9\u3002<\/h3>\n\n\n\n
SELECT RANK() OVER (ORDER BY SUM(sc.grade) DESC) AS rank,
       s.name, SUM(sc.grade) AS total_score
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id;
<\/code><\/pre>\n\n\n\n
38. \u67e5\u8be2\u6240\u6709\u8bfe\u7a0b\u7684\u540d\u79f0\u4ee5\u53ca\u6bcf\u95e8\u8bfe\u7a0b\u6210\u7ee9\u7684\u6807\u51c6\u5dee\u3002<\/h3>\n\n\n\n
SELECT c.course_name, STDDEV(sc.grade) AS grade_stddev
FROM courses c
JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY c.course_id;
<\/code><\/pre>\n\n\n\n
39. \u67e5\u8be2\u6240\u6709\u6559\u5e08\u6559\u6388\u7684\u8bfe\u7a0b\uff0c\u4ee5\u53ca\u8fd9\u4e9b\u8bfe\u7a0b\u7684\u5b66\u751f\u7684\u6700\u9ad8\u6210\u7ee9\u3002<\/h3>\n\n\n\n
SELECT t.name AS teacher_name, c.course_name, MAX(sc.grade) AS max_grade
FROM teachers t
JOIN courses c ON t.teacher_id = c.teacher_id
JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY t.teacher_id, c.course_id;
<\/code><\/pre>\n\n\n\n
40. \u67e5\u8be2\u6210\u7ee9\u5728\u5404\u81ea\u8bfe\u7a0b\u524d3\u540d\u7684\u5b66\u751f\u59d3\u540d\u548c\u6210\u7ee9\u3002<\/h3>\n\n\n\n
SELECT s.name, c.course_name, sc.grade
FROM (
    SELECT student_id, course_id, grade,
           RANK() OVER (PARTITION BY course_id ORDER BY grade DESC) AS rank
    FROM student_courses
) AS ranked_sc
JOIN students s ON ranked_sc.student_id = s.student_id
JOIN courses c ON ranked_sc.course_id = c.course_id
WHERE ranked_sc.rank <= 3;
<\/code><\/pre>\n\n\n\n
41. \u67e5\u8be2\u9009\u4fee\u4e86\u81f3\u5c11\u4e09\u95e8\u8bfe\u7a0b\u5e76\u4e14\u603b\u6210\u7ee9\u8d85\u8fc7250\u5206\u7684\u5b66\u751f\u59d3\u540d\u3002<\/h3>\n\n\n\n
SELECT s.name
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id
HAVING COUNT(sc.course_id) >= 3 AND SUM(sc.grade) > 250;
<\/code><\/pre>\n\n\n\n
42. \u67e5\u8be2\u6bcf\u4e2a\u5b66\u751f\u7684\u8bfe\u7a0b\u5e73\u5747\u6210\u7ee9\uff0c\u5e76\u663e\u793a\u6210\u7ee9\u5927\u4e8e\u8be5\u5b66\u751f\u5e73\u5747\u6210\u7ee9\u7684\u8bfe\u7a0b\u540d\u79f0\u53ca\u5176\u6210\u7ee9\u3002<\/h3>\n\n\n\n
SELECT s.name, c.course_name, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE sc.grade > (
    SELECT AVG(grade)
    FROM student_courses
    WHERE student_id = s.student_id
)
ORDER BY s.name, c.course_name;
<\/code><\/pre>\n\n\n\n
43. \u67e5\u8be2\u6bcf\u95e8\u8bfe\u7a0b\u4e2d\uff0c\u9009\u4fee\u8be5\u8bfe\u7a0b\u7684\u5b66\u751f\u4e2d\u6700\u9ad8\u6210\u7ee9\u548c\u6700\u4f4e\u6210\u7ee9\u4e4b\u95f4\u7684\u5dee\u5f02\u3002<\/h3>\n\n\n\n
SELECT c.course_name, 
       (MAX(sc.grade) - MIN(sc.grade)) AS grade_difference
FROM courses c
JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY c.course_id;
<\/code><\/pre>\n\n\n\n
44. \u67e5\u8be2\u6bcf\u4e2a\u5b66\u751f\u9009\u4fee\u7684\u8bfe\u7a0b\u6570\uff0c\u5e76\u4e14\u7b5b\u9009\u51fa\u90a3\u4e9b\u9009\u4fee\u8bfe\u7a0b\u6570\u6700\u591a\u7684\u5b66\u751f\u3002<\/h3>\n\n\n\n
SELECT s.name, COUNT(sc.course_id) AS course_count
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id
HAVING COUNT(sc.course_id) = (
    SELECT MAX(course_count)
    FROM (
        SELECT COUNT(sc.course_id) AS course_count
        FROM students s
        JOIN student_courses sc ON s.student_id = sc.student_id
        GROUP BY s.student_id
    ) AS course_counts
);
<\/code><\/pre>\n\n\n\n
45. \u67e5\u8be2\u6bcf\u4f4d\u5b66\u751f\u9009\u4fee\u7684\u8bfe\u7a0b\u548c\u5176\u6210\u7ee9\uff0c\u663e\u793a\u6bcf\u4e2a\u5b66\u751f\u6bcf\u95e8\u8bfe\u7a0b\u7684\u6210\u7ee9\u6392\u540d\uff08\u6309\u6210\u7ee9\u964d\u5e8f\uff09\u3002<\/h3>\n\n\n\n
SELECT s.name, c.course_name, sc.grade,
       RANK() OVER (PARTITION BY sc.course_id ORDER BY sc.grade DESC) AS rank
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id;
<\/code><\/pre>\n\n\n\n
46. \u67e5\u8be2\u6210\u7ee9\u5728\u73ed\u7ea7\u4e2d\u524d10%\u7684\u5b66\u751f\u59d3\u540d\u548c\u6210\u7ee9\uff08\u6309\u8bfe\u7a0b\u5206\u7ec4\uff09\u3002<\/h3>\n\n\n\n
SELECT s.name, c.course_name, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE sc.grade >= (
    SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY grade DESC)
    FROM student_courses
    WHERE course_id = c.course_id
)
ORDER BY c.course_name, sc.grade DESC;
<\/code><\/pre>\n\n\n\n
47. \u67e5\u8be2\u6bcf\u4f4d\u8001\u5e08\u6559\u6388\u7684\u8bfe\u7a0b\u53ca\u5176\u6240\u6709\u5b66\u751f\u7684\u6210\u7ee9\uff0c\u5e76\u663e\u793a\u6bcf\u4f4d\u5b66\u751f\u7684\u6210\u7ee9\u5728\u8bfe\u7a0b\u4e2d\u7684\u6392\u540d\u3002<\/h3>\n\n\n\n
SELECT t.name AS teacher_name, c.course_name, s.name AS student_name, sc.grade,
       RANK() OVER (PARTITION BY c.course_id ORDER BY sc.grade DESC) AS grade_rank
FROM teachers t
JOIN courses c ON t.teacher_id = c.teacher_id
JOIN student_courses sc ON c.course_id = sc.course_id
JOIN students s ON sc.student_id = s.student_id;
<\/code><\/pre>\n\n\n\n
48. \u67e5\u8be2\u6240\u6709\u5b66\u751f\u7684\u603b\u6210\u7ee9\uff0c\u5e76\u663e\u793a\u90a3\u4e9b\u6210\u7ee9\u5927\u4e8e\u6240\u6709\u5176\u4ed6\u5b66\u751f\u603b\u6210\u7ee9\u5e73\u5747\u503c\u7684\u5b66\u751f\u3002<\/h3>\n\n\n\n
SELECT s.name, SUM(sc.grade) AS total_score
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id
HAVING SUM(sc.grade) > (
    SELECT AVG(total_score)
    FROM (
        SELECT SUM(sc.grade) AS total_score
        FROM students s
        JOIN student_courses sc ON s.student_id = sc.student_id
        GROUP BY s.student_id
    ) AS total_scores
);
<\/code><\/pre>\n\n\n\n
49. \u67e5\u8be2\u6bcf\u4e2a\u8bfe\u7a0b\u7684\u5b66\u751f\u4eba\u6570\uff0c\u6210\u7ee9\u9ad8\u4e8e\u8be5\u8bfe\u7a0b\u5e73\u5747\u6210\u7ee9\u7684\u5b66\u751f\u7684\u6570\u91cf\u3002<\/h3>\n\n\n\n
SELECT c.course_name, COUNT(sc.student_id) AS total_students,
       COUNT(CASE WHEN sc.grade > avg_grade THEN 1 END) AS above_average_students
FROM courses c
JOIN student_courses sc ON c.course_id = sc.course_id
JOIN (
    SELECT course_id, AVG(grade) AS avg_grade
    FROM student_courses
    GROUP BY course_id
) AS avg_scores ON c.course_id = avg_scores.course_id
GROUP BY c.course_id;
<\/code><\/pre>\n\n\n\n
50. \u67e5\u8be2\u6bcf\u4e2a\u5b66\u751f\u7684\u8bfe\u7a0b\u6210\u7ee9\uff0c\u5982\u679c\u67d0\u4e2a\u5b66\u751f\u7684\u67d0\u95e8\u8bfe\u7a0b\u6210\u7ee9\u4f4e\u4e8e60\u5206\uff0c\u663e\u793a\u5176\u6210\u7ee9\u201c\u5dee\u201d\uff0c\u5426\u5219\u663e\u793a\u5176\u6210\u7ee9\u4e3a\u201c\u5408\u683c\u201d\u3002<\/h3>\n\n\n\n
SELECT s.name, c.course_name, 
       CASE 
           WHEN sc.grade < 60 THEN '\u5dee'
           ELSE '\u5408\u683c'
       END AS grade_status
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id;
<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
\u4e8c.\u7b54\u6848 1. \u67e5\u8be2\u6240\u6709\u5b66\u751f\u7684\u4fe1\u606f\u3002 SELECT * FROM students; 2. \u67e5\u8be2\u6240\u6709\u8001\u5e08\u7684\u4fe1\u606f […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-2743","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/usei.cn\/index.php\/wp-json\/wp\/v2\/posts\/2743","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/usei.cn\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/usei.cn\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/usei.cn\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/usei.cn\/index.php\/wp-json\/wp\/v2\/comments?post=2743"}],"version-history":[{"count":39,"href":"https:\/\/usei.cn\/index.php\/wp-json\/wp\/v2\/posts\/2743\/revisions"}],"predecessor-version":[{"id":2792,"href":"https:\/\/usei.cn\/index.php\/wp-json\/wp\/v2\/posts\/2743\/revisions\/2792"}],"wp:attachment":[{"href":"https:\/\/usei.cn\/index.php\/wp-json\/wp\/v2\/media?parent=2743"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/usei.cn\/index.php\/wp-json\/wp\/v2\/categories?post=2743"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/usei.cn\/index.php\/wp-json\/wp\/v2\/tags?post=2743"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}