1.学生各科成绩前几名数据查询
-- 1) 建表
DROP TABLE IF EXISTS score;
CREATE TABLE score (
id INT PRIMARY KEY,
student_name VARCHAR(50) NOT NULL,
subject VARCHAR(50) NOT NULL,
score INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 2) 插入数据
INSERT INTO score (id, student_name, subject, score) VALUES
(1, '张三', '数学', 95),
(2, '李四', '数学', 90),
(3, '王五', '数学', 98),
(4, '赵六', '数学', 88),
(5, '张三', '英语', 92),
(6, '李四', '英语', 85),
(7, '王五', '英语', 96),
(8, '赵六', '英语', 90);
explain SELECT *
FROM (
SELECT *,
DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS rk
FROM score
) t
WHERE rk <= 3;
SELECT a.*
FROM score a
WHERE (
SELECT COUNT(*)
FROM score b
WHERE a.subject = b.subject
AND b.score > a.score
) < 3
ORDER BY a.subject, a.score DESC;
explain SELECT
a.*,
(
SELECT COUNT(*) + 1
FROM score b
WHERE a.subject = b.subject
AND b.score > a.score
) AS rk
FROM score a
WHERE (
SELECT COUNT(*)
FROM score b
WHERE a.subject = b.subject
AND b.score > a.score
) < 3
ORDER BY a.subject, rk;
2.用户性别统计
-- 建表示例（如果你已经有表，可跳过）
DROP TABLE IF EXISTS user;
CREATE TABLE user (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
gender VARCHAR(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入示例数据
INSERT INTO user (id, name, gender) VALUES
(1, '张三', 'male'),
(2, '李四', 'male'),
(3, '王五', 'female'),
(4, '赵六', 'female'),
(5, '钱七', 'not to say'),
(6, '孙八', 'male'),
(7, '周九', 'female'),
(8, '吴十', 'male'),
(9, '郑十一', 'not to say'),
(10, '王十二', 'female'),
(11, '刘十三', 'male'),
(12, '陈十四', 'female'),
(13, '杨十五', 'not to say'),
(14, '黄十六', 'male'),
(15, '林十七', 'female');
explain SELECT
gender,
COUNT(*) AS total
FROM user
GROUP BY gender having gender in ("male","female") #有点不用修改sql代码,程序映射处理成一行数据。in 参数传递
explain SELECT
SUM(CASE WHEN gender = 'male' THEN 1 ELSE 0 END) AS male_total,
SUM(CASE WHEN gender = 'female' THEN 1 ELSE 0 END) AS female_total,
SUM(CASE WHEN gender = 'not to say' THEN 1 ELSE 0 END) AS not_to_say_total
FROM user;
3.部门职员工资统计
-- 1) 部门表
DROP TABLE IF EXISTS dept;
CREATE TABLE dept (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 2) 员工表
DROP TABLE IF EXISTS emp;
CREATE TABLE emp (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50) NOT NULL,
salary DECIMAL(10,2) NOT NULL,
dept_id INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 3) 插入部门数据
INSERT INTO dept (dept_id, dept_name) VALUES
(10, '研发部'),
(20, '产品部'),
(30, '运营部'),
(40, '财务部'),
(50, '人事部');
-- 4) 插入员工数据
INSERT INTO emp (emp_id, emp_name, salary, dept_id) VALUES
(1001, '张三', 18000.00, 10),
(1002, '李四', 22000.00, 10),
(1003, '王五', 19500.00, 10),
(2001, '赵六', 16000.00, 20),
(2002, '钱七', 17500.00, 20),
(3001, '孙八', 12000.00, 30),
(3002, '周九', 13500.00, 30),
(3003, '吴十', 11000.00, 30),
(3004, '郑十一', 15000.00, 30),
(4001, '王十二', 14000.00, 40),
(4002, '刘十三', 15500.00, 40),
(5001, '陈十四', 13000.00, 50);
SELECT
d.dept_id,
d.dept_name,
AVG(e.salary) AS avg_salary,
MIN(e.salary) AS min_salary,
MAX(e.salary) AS max_salary
FROM dept d
LEFT JOIN emp e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY d.dept_id;
select tmp.*,dept.dept_name from (
select *,dense_rank() over (partition by dept_id order by salary desc) as rk from emp) tmp
left join dept on dept.dept_id=tmp.dept_id
ALTER TABLE emp ADD COLUMN income_range VARCHAR(30);
UPDATE emp
SET income_range = CASE
WHEN salary < 10000 THEN '<10000'
WHEN salary < 15000 THEN '10000-14999'
WHEN salary < 20000 THEN '15000-19999'
ELSE '>=20000'
END;
select x.dept_id,x.income_range,dept.dept_name from
(SELECT dept_id, income_range, COUNT(*) AS cnt
FROM emp
GROUP BY dept_id, income_range) x
join
(SELECT dept_id, MAX(cnt) AS max_cnt
FROM (
SELECT dept_id, income_range, COUNT(*) AS cnt
FROM emp
GROUP BY dept_id, income_range
) t
GROUP BY dept_id) m on x.dept_id=m.dept_id AND x.cnt = m.max_cnt
join dept on x.dept_id=dept.dept_id ORDER BY x.dept_id, x.income_range;
4.树形表数据设计（推荐设置paths(通过like查询处理),path_names,level,必选上级parent_id）
DROP TABLE IF EXISTS dept;
CREATE TABLE dept (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL,
parent_dept_id INT NULL,
sort_no INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 索引（递归/树查询非常常用）
CREATE INDEX idx_dept_parent ON dept(parent_dept_id);
CREATE INDEX idx_dept_sort ON dept(parent_dept_id, sort_no, dept_id);
-- 清空数据（可选）
TRUNCATE TABLE dept;
-- 先插入根部门（parent_dept_id = NULL）
INSERT INTO dept (dept_id, dept_name, parent_dept_id, sort_no) VALUES
(1, '总部', NULL, 1),
(2, '华东大区', NULL, 2),
(3, '华南大区', NULL, 3);
-- 再插入二级部门
INSERT INTO dept (dept_id, dept_name, parent_dept_id, sort_no) VALUES
(10, '技术中心', 1, 1),
(20, '业务中心', 1, 2),
(30, '职能中心', 1, 3),
(210, '上海分部', 2, 1),
(220, '杭州分部', 2, 2),
(310, '广州分部', 3, 1),
(320, '深圳分部', 3, 2);
-- 三级部门（技术中心下）
INSERT INTO dept (dept_id, dept_name, parent_dept_id, sort_no) VALUES
(101, '研发部', 10, 1),
(102, '数据平台部', 10, 2),
(103, '测试部', 10, 3),
(104, '运维部', 10, 4);
-- 三级部门（业务中心下）
INSERT INTO dept (dept_id, dept_name, parent_dept_id, sort_no) VALUES
(201, '销售部', 20, 1),
(202, '市场部', 20, 2),
(203, '客户成功部', 20, 3);
-- 三级部门（职能中心下）
INSERT INTO dept (dept_id, dept_name, parent_dept_id, sort_no) VALUES
(301, '财务部', 30, 1),
(302, '人事部', 30, 2),
(303, '行政部', 30, 3);
-- 四级部门（示例：数据平台部下再拆）
INSERT INTO dept (dept_id, dept_name, parent_dept_id, sort_no) VALUES
(1021, '数据仓库组', 102, 1),
(1022, '实时计算组', 102, 2),
(1023, 'BI分析组', 102, 3);
WITH RECURSIVE dept_tree AS (
-- 根节点
SELECT
d.dept_id,
d.dept_name,
d.parent_dept_id,
1 AS level,
CAST(d.dept_id AS CHAR(200)) AS path_ids,
CAST(d.dept_name AS CHAR(500)) AS path_names
FROM dept d
WHERE d.parent_dept_id IS NULL
UNION ALL
-- 递归向下找子节点
SELECT
c.dept_id,
c.dept_name,
c.parent_dept_id,
p.level + 1 AS level,
CONCAT(p.path_ids, '/', c.dept_id) AS path_ids,
CONCAT(p.path_names, '/', c.dept_name) AS path_names
FROM dept c
JOIN dept_tree p ON c.parent_dept_id = p.dept_id
WHERE p.path_ids NOT LIKE CONCAT(c.dept_id, '/%')
)
SELECT *
FROM dept_tree
ORDER BY path_ids;
WITH RECURSIVE dept_tree AS (
-- 起点部门（根：dept_id = 10）
SELECT
dept_id,
dept_name,
parent_dept_id,
1 AS level,
CAST(dept_id AS CHAR(200)) AS path_ids,
CAST(dept_name AS CHAR(500)) AS path_names
FROM dept
WHERE dept_id = 10
UNION ALL
-- 递归向下拼接路径
SELECT
c.dept_id,
c.dept_name,
c.parent_dept_id,
p.level + 1 AS level,
CONCAT(p.path_ids, '/', c.dept_id) AS path_ids,
CONCAT(p.path_names, '/', c.dept_name) AS path_names
FROM dept c
JOIN dept_tree p ON c.parent_dept_id = p.dept_id
)
SELECT
dept_id, dept_name, parent_dept_id, level, path_ids, path_names
FROM dept_tree
ORDER BY level, dept_id;
备注：面试这么做,实际都是查询数据到应用程序,使用应用程序处理成树形表。
