高考志愿 SQL 应用案例分析 高考志愿 SQL 在数据处理中的核心功能与优势

school_info

SELECT school_name, major_name, score_line
FROM school_info
WHERE interest = '学生的兴趣'
AND score_line BETWEEN '学生分数' - 10 AND '学生分数' + 10
ORDER BY ABS('学生分数' - score_line) ASC
LIMIT 10;

ORDER BY

LIMIT

SELECT school_name, major_name, score_line
FROM school_info
WHERE interest = '学生的兴趣'
AND score_line BETWEEN '学生分数' - 10 AND '学生分数' + 10
AND location = '学生期望的城市'
ORDER BY ABS('学生分数' - score_line) ASC
LIMIT 10;

admission_history

SELECT school_name, major_name, AVG(score_line) AS avg_score, STDDEV(score_line) AS std_dev
FROM admission_history
GROUP BY school_name, major_name
ORDER BY avg_score DESC;

-- 这只是一个概念性的示例，实际应用需要更复杂的统计模型
SELECT school_name, major_name,
       (SELECT AVG(score_line) + (SELECT COVAR(year, score_line) / VAR_POP(year) FROM admission_history WHERE school_name = a.school_name AND major_name = a.major_name) * (2024 - AVG(year))
        FROM admission_history WHERE school_name = a.school_name AND major_name = a.major_name) AS predicted_score
FROM (SELECT DISTINCT school_name, major_name FROM admission_history) AS a;

student_rank

admission_data

SELECT school_name, COUNT(*) AS accepted_count
FROM admission_data
WHERE rank BETWEEN '学生排名' - 100 AND '学生排名' + 100
GROUP BY school_name
ORDER BY accepted_count DESC
LIMIT 10;

SELECT school_name, MIN(rank) AS min_rank
FROM admission_data
WHERE year = '历年年份'
GROUP BY school_name
ORDER BY min_rank ASC;