数据分析相关面试题-SQL部分

张

张建站

2026/5/25 15:33:49

10分钟阅读

数据分析相关面试题汇总目录基础语法WHERE 和 HAVING 的区别DISTINCT 和 GROUP BY 的区别LIMIT 和 OFFSETNULL 判断聚合函数COUNT (*)、COUNT (1)、COUNT(列)SUM/AVG 遇到 NULL聚合函数能否嵌套分组统计数量、总和、平均值分组后平均值 100 的组多表 JOIN四种 JOIN 区别LEFT JOIN 左表一定全保留吗JOIN 数据膨胀原因ON 与 WHERE 在 LEFT JOIN 中区别子查询与 WITH相关子查询 vs 非相关子查询EXISTS 与 IN查询最大值所在整行大于本组平均值CASE WHEN顺序匹配行转列统计数量CASE 可放位置MySQL IF 与 CASE WHEN窗口函数三个排名函数区别窗口函数结构窗口函数能否放 WHERE每组 TopN累计和LAG、LEAD窗口 SUM 与 GROUP BY 区别去重、合并、排序UNION 与 UNION ALL查询重复数据删除重复保留一条多字段排序NULL 排序位置SQL 执行顺序标准执行顺序为什么别名不能用在 WHERESQL 优化定位慢查询索引作用索引失效场景不推荐 SELECT *大表 JOIN 优化高频手写综合题带注释分组统计过滤行转列每组 Top3连续 N 天登录通用模板环比计算SQL应用案例-高等教育类统一表结构基础查询GROUP BY 分组聚合空值、去重、COUNT多表 JOIN子查询 HAVINGCASE WHEN窗口函数深造 / 毕业 / 报到专项 SQL高频简答题基础语法WHERE 和 HAVING 的区别WHERE对【原始数据行】过滤发生在分组之前HAVING对【分组后的结果】过滤必须跟在 GROUP BY 后可使用聚合函数WHERE 不能用聚合函数HAVING 可以DISTINCT 和 GROUP BY 的区别DISTINCT对结果集去重只保留唯一值不做聚合GROUP BY按字段分组用于配合聚合函数做统计结论只去重用 DISTINCT要统计用 GROUP BYSELECT DISTINCT col FROM t;SELECT col, COUNT(*) FROM t GROUP BY col;LIMIT 和 OFFSETLIMIT n取前 n 条OFFSET m跳过前 m 条等价于 LIMIT 5,10作用分页查询SELECT * FROM t LIMIT 10 OFFSET 5;NULL 判断错误col NULL 永远不成立NULL 不等于任何值包括自己正确判断是否为空用 IS NULL / IS NOT NULLSELECT * FROM t WHERE col IS NULL; SELECT * FROM t WHERE col IS NOT NULL;别名能否用于 WHERE不能因为 SQL 执行顺序WHERE 早于 SELECT错误SELECT col AS a FROM t WHERE a 10;正确SELECT col AS a FROM t WHERE col 10;聚合函数COUNT (*)、COUNT (1)、COUNT(列)COUNT(*)统计所有行包含 NULLCOUNT(1)与 COUNT(*) 几乎一致效率基本相同COUNT(列)只统计【该列不为 NULL】的行数 -- 面试结论统计表总行数用 COUNT(*)统计非空用 COUNT(列)SUM/AVG 遇到 NULLSUM、AVG、MAX、MIN 都会自动忽略 NULL不参与计算例如 AVG(10, NULL, 20) (1020)/2 15聚合函数能否嵌套不能直接嵌套SUM(AVG(col)) 报错必须用子查询/窗口函数间接实现SELECT SUM(avg_col) FROM (SELECT AVG(col) AS avg_col FROM t GROUP BY id) tmp;分组统计数量、总和、平均值注意SELECT 中非聚合字段必须出现在 GROUP BY 中SELECT col1, -- 分组字段 COUNT(*) AS cnt, -- 每组行数 SUM(col2) AS sum_col,-- 求和 AVG(col2) AS avg_col -- 平均值 FROM t GROUP BY col1;分组后平均值 100 的组HAVING 过滤分组结果SELECT col1, AVG(col2) AS avg_col FROM t GROUP BY col1 HAVING AVG(col2) 100;多表 JOIN四种 JOIN 区别INNER JOIN只保留两边都能匹配上的行LEFT JOIN左表全部保留右表匹配不上补 NULLRIGHT JOIN右表全部保留左表匹配不上补 NULLFULL JOIN左右表都保留匹配不上补 NULLMySQL 不支持可用 UNION 模拟LEFT JOIN 左表一定全保留吗不一定如果在 WHERE 里对右表字段做过滤IS NOT NULL / 值会把 LEFT JOIN 变成 INNER JOINJOIN 数据膨胀原因一对多关系1 条左表数据匹配多条右表数据例如1 个用户对应 5 条订单JOIN 后行数变多解决先聚合右表再 JOIN或用 DISTINCT 去重ON 与 WHERE 在 LEFT JOIN 中区别ONJOIN 时的匹配条件不影响左表行数WHERE对 JOIN 后的结果整体过滤会删除左表行示例左表全保留只匹配 t2.status1 的行SELECT * FROM t1 LEFT JOIN t2 ON t1.id t2.id AND t2.status 1;示例等价 INNER JOIN左表不满足行会被删掉SELECT * FROM t1 LEFT JOIN t2 ON t1.id t2.id WHERE t2.status 1;子查询与 WITH相关子查询 vs 非相关子查询非相关子查询子查询可独立运行只执行一次SELECT * FROM t WHERE col IN (SELECT col FROM tmp);相关子查询子查询依赖外层表每行执行一次SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.id t2.id);EXISTS 与 ININ适合子查询结果小EXISTS适合子查询结果大逐行匹配效率更高优先用 EXISTS查询最大值所在整行方法1子查询SELECT * FROM t WHERE col (SELECT MAX(col) FROM t);方法2窗口函数SELECT * FROM ( SELECT *, RANK() OVER(ORDER BY col DESC) AS rk FROM t ) tmp WHERE rk 1;大于本组平均值SELECT t.* FROM t JOIN ( SELECT group_id, AVG(col) AS group_avg FROM t GROUP BY group_id ) tmp ON t.group_id tmp.group_id WHERE t.col tmp.group_avg;WITH AS 临时表公共表达式提高可读性可多次复用WITH tmp AS ( SELECT group_id, AVG(col) AS avg_col FROM t GROUP BY group_id ) SELECT * FROM tmp WHERE avg_col 100;CASE WHEN顺序匹配按顺序匹配满足第一个即停止SELECT score, CASE WHEN score 90 THEN 优秀 WHEN score 70 THEN 良好 WHEN score 60 THEN 及格 ELSE 不及格 END AS level FROM t;行转列统计数量不满足条件为 NULLCOUNT 忽略 NULLSELECT COUNT(CASE WHEN gender 1 THEN 1 END) AS male_cnt, COUNT(CASE WHEN gender 0 THEN 1 END) AS female_cnt FROM t;CASE 可放位置SELECT、WHERE、GROUP BY、HAVING、ORDER BY 都可以用MySQL IF 与 CASE WHENIF(条件, 成立值, 不成立值) 只能单分支CASE WHEN 支持多条件更通用IF(score60, 及格, 不及格)窗口函数三个排名函数区别ROW_NUMBER()连续不重复排名 1,2,3,4RANK()并列排名跳号 1,1,3,4DENSE_RANK()并列排名不跳号 1,1,2,3SELECT id, score, ROW_NUMBER() OVER(ORDER BY score DESC) AS rn, RANK() OVER(ORDER BY score DESC) AS rk, DENSE_RANK() OVER(ORDER BY score DESC) AS drk FROM t;窗口函数结构不改变行数对每行计算一个统计值OVER ( PARTITION BY group_id -- 分组可选 ORDER BY col DESC -- 排序必须 )窗口函数能否放 WHERE不能执行顺序WHERE → GROUP BY → 窗口函数 → ORDER BY → LIMIT窗口函数计算晚于 WHERE所以不能直接过滤每组 TopNSELECT * FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY score DESC) AS rn FROM t ) tmp WHERE rn 3;累计和SELECT id, val, SUM(val) OVER(ORDER BY id) AS cum_sum -- 按顺序累加 FROM t;LAG、LEADLAG(col, n)取当前行【前n行】的值LEAD(col, n)取当前行【后n行】的值常用于环比、同比、连续问题SELECT id, LAG(val, 1) OVER(ORDER BY id) AS pre_val, LEAD(val, 1) OVER(ORDER BY id) AS next_val FROM t;窗口 SUM 与 GROUP BY 区别GROUP BY分组后【行数变少】SUM() OVER(PARTITION BY)保留所有行附加分组统计值去重、合并、排序UNION 与 UNION ALLUNION合并结果并【去重排序】慢UNION ALL直接拼接【不去重】快优先用 UNION ALL查询重复数据SELECT col, COUNT(*) FROM t GROUP BY col HAVING COUNT(*) 1;删除重复保留一条保留 id 最小的一条DELETE t1 FROM t t1 JOIN t t2 WHERE t1.col t2.col AND t1.id t2.id;多字段排序先按 col1 降序相同再按 col2 升序SELECT * FROM t ORDER BY col1 DESC, col2 ASC;NULL 排序位置MySQLNULL 视为最小值升序在前降序在后如需把 NULL 放最后ORDER BY ISNULL(col), col;SQL 执行顺序标准执行顺序1. FROM / JOIN 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT含窗口函数、别名 6. DISTINCT 7. ORDER BY 8. LIMIT / OFFSET为什么别名不能用在 WHERE别名在 SELECT 阶段生成WHERE 执行早于 SELECT所以看不到别名SQL 优化定位慢查询开启慢查询日志使用 EXPLAIN 查看执行计划看是否走索引、是否全表扫描、是否出现 filesort索引作用索引是排序结构类似目录作用避免全表扫描加速 WHERE / ORDER BY / GROUP BY索引失效场景字段使用函数WHERE YEAR(date) 2024隐式类型转换LIKE %xxx% 前模糊OR 连接无索引字段优化器认为全表更快数据量小不推荐 SELECT *查询无用字段增加 IO无法使用覆盖索引表结构变化后易出错大表 JOIN 优化小表驱动大表关联字段建索引先过滤再 JOIN避免一对多导致数据膨胀必要时分批查询高频手写综合题带注释分组统计过滤SELECT dept_id, COUNT(*) AS cnt, AVG(salary) AS avg_sal FROM employee WHERE salary 0 GROUP BY dept_id HAVING avg_sal 5000 ORDER BY avg_sal DESC LIMIT 10;行转列SELECT user_id, SUM(CASE WHEN type 1 THEN amount END) AS income, SUM(CASE WHEN type 2 THEN amount END) AS outcome FROM bill GROUP BY user_id;每组 Top3SELECT * FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employee ) tmp WHERE rn 3;连续 N 天登录通用模板SELECT user_id FROM ( SELECT user_id, dt, -- 日期 - 行号连续日期会得到相同值 DATE_SUB(dt, INTERVAL ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY dt) DAY) AS grp FROM login_log ) tmp GROUP BY user_id, grp HAVING COUNT(*) 7;环比计算SELECT dt, val, LAG(val, 1) OVER(ORDER BY dt) AS pre_val, ROUND((val - LAG(val,1) OVER(ORDER BY dt))/LAG(val,1) OVER(ORDER BY dt),2) AS ratio FROM daily_data;SQL应用案例-高等教育类统一表结构1. 录取/生源表存储学生高考录取、生源、专业信息CREATE TABLE student_admit ( student_id VARCHAR(20) PRIMARY KEY, -- 学号主键唯一标识学生 college VARCHAR(50), -- 学院 major VARCHAR(50), -- 专业 province VARCHAR(50), -- 生源省份 score INT, -- 录取分数 gender VARCHAR(10), -- 性别 is_first_choice TINYINT, -- 1一志愿 0调剂 admit_year INT, -- 录取年份 is_recruit TINYINT -- 1统招 0单招/综评 );2. 报到表存储新生报到状态CREATE TABLE student_register ( student_id VARCHAR(20) PRIMARY KEY, -- 学号关联生源表 is_register TINYINT, -- 1已报到 0未报到 register_time DATETIME -- 报到时间 );3. 毕业表存储学生毕业、学分、挂科信息CREATE TABLE student_graduate ( student_id VARCHAR(20) PRIMARY KEY, -- 学号 graduate_year INT, -- 毕业年份 is_graduate TINYINT, -- 1正常毕业 0未毕业 is_degree TINYINT, -- 1授予学位 0不授予 fail_course INT, -- 挂科门数 credit_complete DECIMAL(5,2) -- 已修学分 );4. 深造/升学表存储学生保研、考研、出国信息CREATE TABLE student_advance ( student_id VARCHAR(20) PRIMARY KEY, -- 学号 is_recommend TINYINT, -- 1保研 0未保研 is_postgrad TINYINT, -- 1考研上岸 0未上岸 is_abroad TINYINT, -- 1出国出境 0未出国 target_school_type VARCHAR(20) -- 录取院校类型985/211/双一流/普通 );基础查询查询 2024 级学生核心信息需求筛选2024年录取学生展示学号、学院、专业、分数、省份SELECT student_id, college, major, score, province FROM student_admit WHERE admit_year 2024; -- 按录取年份过滤查询未报到新生按分数降序需求关联生源表和报到表筛选未报到学生按录取分数排序SELECT a.* -- 查询录取表所有字段 FROM student_admit a LEFT JOIN student_register r ON a.student_id r.student_id -- 左连接保证所有录取学生都保留 WHERE r.is_register 0 OR r.is_register IS NULL -- 0未报到NULL无报到记录视为未报到 ORDER BY score DESC; -- 按录取分数降序河南生源 500 分以上学生需求多条件过滤省份河南省分数500SELECT * FROM student_admit WHERE province 河南省 AND score 500;查询含 “工程” 的专业及招生人数需求模糊匹配专业名分组统计招生人数SELECT major, COUNT(*) AS enroll_num -- 统计每个专业人数 FROM student_admit GROUP BY major HAVING major LIKE %工程%; -- 分组后过滤专业名去重统计生源省份数量需求统计不重复的省份总数SELECT COUNT(DISTINCT province) AS province_cnt FROM student_admit; -- DISTINCT 去重COUNT统计数量GROUP BY 分组聚合各学院录取人数、报到人数、报到率需求按学院分组计算报到率报到人数/录取人数SELECT a.college, COUNT(*) AS admit_cnt, -- 录取总人数 SUM(r.is_register) AS register_cnt, -- 报到人数is_register1求和 -- 计算报到率保留2位小数*100.0转为浮点避免整数除法 ROUND(SUM(r.is_register)*100.0/COUNT(*),2) AS register_rate FROM student_admit a LEFT JOIN student_register r ON a.student_id r.student_id GROUP BY a.college; -- 按学院分组各专业录取分数统计需求按专业分组计算平均分、最高分、最低分SELECT major, AVG(score) AS avg_score, -- 平均分 MAX(score) AS max_score, -- 最高分 MIN(score) AS min_score -- 最低分 FROM student_admit GROUP BY major;各省生源人数排序需求按省份分组统计人数按人数降序SELECT province, COUNT(*) AS cnt FROM student_admit GROUP BY province ORDER BY cnt DESC;男女学生人数及占比需求按性别分组计算人数和占比SELECT gender, COUNT(*) AS cnt, -- 窗口函数SUM(COUNT(*))OVER()计算总人数求占比 ROUND(COUNT(*)*100.0/SUM(COUNT(*))OVER(),2) AS ratio FROM student_admit GROUP BY gender;各专业一志愿率统计需求按专业分组计算一志愿录取率SELECT major, SUM(is_first_choice) AS first_cnt, -- 一志愿录取人数 COUNT(*) AS total, -- 总录取人数 ROUND(SUM(is_first_choice)*100.0/COUNT(*),2) AS first_rate -- 一志愿率 FROM student_admit GROUP BY major;空值、去重、COUNT统计录取分数为空的学生数SELECT COUNT(*) FROM student_admit WHERE score IS NULL;统计省份非空的有效生源数SELECT COUNT(*) FROM student_admit WHERE province IS NOT NULL;查询重复学号的学生需求按学号分组筛选出现次数1的重复数据SELECT student_id, COUNT(*) FROM student_admit GROUP BY student_id HAVING COUNT(*) 1;删除重复学号保留一条需求自连接删除重复数据保留学号最小的一条DELETE t1 FROM student_admit t1 JOIN student_admit t2 ON t1.student_id t2.student_id WHERE t1.ctid t2.ctid;多表 JOIN各专业报到率统计需求按专业分组关联报到表计算报到率SELECT a.major, COUNT(*) AS admit_cnt, SUM(r.is_register) AS register_cnt, ROUND(SUM(r.is_register)*100.0/COUNT(*),2) AS register_rate FROM student_admit a LEFT JOIN student_register r USING(student_id) -- USING简化关联字段写法 GROUP BY a.major;所有录取学生报到状态需求左连接保证所有录取学生保留无报到记录填0SELECT a.*, COALESCE(r.is_register, 0) AS is_register -- COALESCENULL替换为0 FROM student_admit a LEFT JOIN student_register r USING(student_id);各专业学位授予率需求关联毕业表计算学位授予比例SELECT a.major, COUNT(*) AS total, -- 总毕业人数 SUM(g.is_degree) AS degree_cnt, -- 授予学位人数 ROUND(SUM(g.is_degree)*100.0/COUNT(*),2) AS degree_rate -- 学位授予率 FROM student_admit a JOIN student_graduate g USING(student_id) GROUP BY a.major;三表关联各省生源深造人数需求生源报到深造三表关联按省份统计深造人数SELECT a.province, COUNT(DISTINCT a.student_id) AS student_cnt, -- 去重统计总人数 -- CASE判断满足任一深造条件记为1求和得深造人数 SUM(CASE WHEN adv.is_recommend1 OR adv.is_postgrad1 OR adv.is_abroad1 THEN 1 ELSE 0 END) AS advance_cnt FROM student_admit a LEFT JOIN student_register r USING(student_id) LEFT JOIN student_advance adv USING(student_id) GROUP BY a.province;子查询 HAVING分数高于全校平均分的学生需求子查询先算全校平均分再过滤学生SELECT * FROM student_admit WHERE score (SELECT AVG(score) FROM student_admit);报到率低于 85% 的专业需求子查询先计算各专业报到率外层过滤SELECT major, register_rate FROM ( SELECT major, ROUND(SUM(r.is_register)*100.0/COUNT(*),2) AS register_rate FROM student_admit a LEFT JOIN student_register r USING(student_id) GROUP BY major ) t -- 临时表 WHERE register_rate 85;高于本专业平均分的学生需求先计算各专业平均分再关联原表过滤SELECT a.* FROM student_admit a JOIN ( SELECT major, AVG(score) AS avg_major_score FROM student_admit GROUP BY major ) m ON a.major m.major -- 按专业匹配平均分 WHERE a.score m.avg_major_score; -- 分数专业平均分近三年录取人数逐年上升的专业需求窗口函数LAG获取上一年录取人数对比逐年上升SELECT major, admit_year, cnt FROM ( SELECT major, admit_year, COUNT(*) AS cnt, -- LAG取上一年数据按专业分组、年份排序 LAG(COUNT(*),1) OVER(PARTITION BY major ORDER BY admit_year) AS pre_cnt FROM student_admit WHERE admit_year IN (2022,2023,2024) GROUP BY major, admit_year ) t WHERE cnt pre_cnt; -- 今年人数去年人数报到毕业深造的学生总数需求三表内连接筛选同时满足三个条件的学生SELECT COUNT(*) AS total FROM student_register r JOIN student_graduate g USING(student_id) JOIN student_advance adv USING(student_id) WHERE r.is_register1 -- 已报到 AND g.is_graduate1 -- 已毕业 -- 满足任一深造条件 AND (adv.is_recommend1 OR adv.is_postgrad1 OR adv.is_abroad1);CASE WHEN各专业总深造率需求深造保研考研出国计算各专业深造比例SELECT a.major, COUNT(*) AS graduate_cnt, SUM(CASE WHEN adv.is_recommend1 OR adv.is_postgrad1 OR adv.is_abroad1 THEN 1 ELSE 0 END) AS advance_cnt, ROUND(SUM(CASE WHEN is_recommendis_postgradis_abroad1 THEN 1 ELSE 0 END)*100.0/COUNT(*),2) AS advance_rate FROM student_admit a JOIN student_graduate g USING(student_id) LEFT JOIN student_advance adv USING(student_id) GROUP BY a.major;分数分段深造率统计需求CASE分数分段统计每段人数和深造率SELECT CASE WHEN score 500 THEN 低分段 WHEN score BETWEEN 500 AND 550 THEN 中段 ELSE 高分段 END AS score_level, -- 分数分段别名 COUNT(*) AS cnt, ROUND(SUM(CASE WHEN is_recommendis_postgradis_abroad1 THEN 1 ELSE 0 END)*100.0/COUNT(*),2) AS advance_rate FROM student_admit a LEFT JOIN student_advance adv USING(student_id) GROUP BY score_level;标记高分生源高于校平均分子查询计算校平均分CASE判断是否高分SELECT *, -- 子查询计算校平均分CASE判断是否高分 CASE WHEN score (SELECT AVG(score) FROM student_admit) THEN 1 ELSE 0 END AS is_high_score FROM student_admit;考研报名率、上岸率SELECT major, SUM(is_postgrad_apply) AS apply_cnt, -- 考研报名人数 SUM(is_postgrad) AS pass_cnt, -- 上岸人数 ROUND(SUM(is_postgrad)*100.0/SUM(is_postgrad_apply),2) AS pass_rate -- 上岸率 FROM student_admit a JOIN student_advance adv USING(student_id) GROUP BY major;毕业结果分类统计需求按毕业状态、学分分类统计SELECT CASE WHEN is_graduate1 THEN 正常毕业 WHEN credit_complete 120 THEN 延期毕业 ELSE 结业/肄业 END AS graduate_type, COUNT(*) AS cnt FROM student_graduate GROUP BY graduate_type;窗口函数专业内分数 Top10需求按专业分组分数降序排名取每组前10SELECT * FROM ( SELECT *, -- 分组内排名连续不重复 ROW_NUMBER() OVER(PARTITION BY major ORDER BY score DESC) AS rn FROM student_admit ) t WHERE rn 10;同专业分数分位数需求计算学生分数在专业内的排名百分比SELECT student_id, major, score, PERCENT_RANK() OVER(PARTITION BY major ORDER BY score) AS pct FROM student_admit;专业人数占学院比例需求按学院专业分组计算专业人数占学院总人数比例SELECT college, major, COUNT(*) AS cnt, -- 窗口函数按学院分组求和计算占比 ROUND(COUNT(*)*100.0/SUM(COUNT(*))OVER(PARTITION BY college),2) AS ratio_in_college FROM student_admit GROUP BY college, major;深造 / 毕业 / 报到专项 SQL各专业毕业率、延期毕业人数SELECT a.major, COUNT(*) AS total, SUM(g.is_graduate) AS graduate_cnt, ROUND(SUM(g.is_graduate)*100.0/COUNT(*),2) AS graduate_rate, -- 筛选未毕业学分不足的延期学生 SUM(CASE WHEN g.is_graduate0 AND g.credit_complete120 THEN 1 ELSE 0 END) AS delay_cnt FROM student_admit a JOIN student_graduate g USING(student_id) GROUP BY a.major;各专业深造明细总深造率SELECT major, SUM(is_recommend) AS recommend_cnt, -- 保研人数 SUM(is_postgrad) AS postgrad_cnt, -- 考研上岸人数 SUM(is_abroad) AS abroad_cnt, -- 出国人数 -- 总深造率满足任一深造条件/总人数 ROUND(SUM(CASE WHEN is_recommendis_postgradis_abroad1 THEN 1 ELSE 0 END)*100.0/COUNT(*),2) AS total_advance_rate FROM student_admit a JOIN student_advance adv USING(student_id) GROUP BY major;各省生源深造率SELECT province, COUNT(*) AS cnt, ROUND(SUM(CASE WHEN is_recommendis_postgradis_abroad1 THEN 1 ELSE 0 END)*100.0/COUNT(*),2) AS advance_rate FROM student_admit a LEFT JOIN student_advance adv USING(student_id) GROUP BY province;升学院校类型占比SELECT target_school_type, COUNT(*) AS cnt, ROUND(COUNT(*)*100.0/SUM(COUNT(*))OVER(),2) AS ratio -- 占总深造人数比例 FROM student_advance WHERE target_school_type IS NOT NULL GROUP BY target_school_type;高频简答题1. WHERE vs HAVINGWHERE 分组前过滤原始行不能用聚合HAVING 分组后过滤结果可以用聚合。2. JOIN vs 子查询JOIN 性能更优索引生效更快优先使用 JOIN。3. 窗口函数限制不能放在 WHERE 中执行顺序晚于 WHERE。4. SQL 优化关联字段建索引、先过滤再 JOIN、避免 SELECT*、减少数据膨胀、EXPLAIN 分析执行计划。5. 口径不一致排查核对时间范围、是否去重、是否包含未报到 / 延期、关联逻辑、统计口径。