SQL
数据
-- 创建工资表 tsalary
CREATE TABLE IF NOT EXISTS t_salary (
id INT PRIMARY KEY AUTO_INCREMENT, -- 假设id是一个自增的主键
dep_no VARCHAR(10), -- 部门编号
user_no VARCHAR(10), -- 用户编号
user_name VARCHAR(50), -- 姓名
month VARCHAR(7), -- 年月，格式为 'YYYY-MM'
salary DECIMAL(10, 2) -- 工资，假设工资精确到小数点后两位
);
-- 插入数据到工资表 tsalary
INSERT INTO t_salary (dep_no, user_no, user_name, month, salary) VALUES
('101', '001', '小王', '2024-01', 100),
('101', '001', '小王', '2024-02', 200),
('101', '002', '小李', '2024-02', 140),
('101', '001', '小王', '2024-03', 500);
-- 创建部门表 t_department
CREATE TABLE if not EXISTS t_department (
id INT PRIMARY KEY AUTO_INCREMENT, -- 假设id是一个自增的主键
dep_no VARCHAR(10), -- 部门编号
dep_name VARCHAR(50), -- 部门名称
user_count INT -- 部门人数
);
-- 插入数据到部门表 t_department
INSERT INTO t_department (dep_no, dep_name, user_count) VALUES
('101', '市场部', 4);
问题一
编写SQL查询每个人每月的累计工资（1月=1月，2月=2月+1月，3月=3月+2月+1月......）
SQL实现
SELECT
user_name as '姓名',
month as '年月',
(
SELECT
SUM(salary)
FROM t_salary
WHERE user_no = outer_user.user_no AND month <= outer_user.month -- 筛选 小于等于 当前日期的数据
) AS '累计工资'
FROM
(
SELECT
DISTINCT user_no
,user_name
,month
FROM t_salary
) AS outer_user
ORDER BY
user_no,
STR_TO_DATE(month, '%Y-%m');
输出示例
姓名 年月 累计工资
小王 2024-01 100.00
小王 2024-02 300.00
小王 2024-03 800.00
小李 2024-02 140.00
问题二
编写SQL查询每个月平均工资在每个段位的人数，[0, 100) A，[100, 200) B ，[200, 300) C，300及以上 D
SQL实现
select
month AS '年月'
,sum(case when avg_salary >= 0 and avg_salary < 100 then 1 else 0 end) as 'A人数'
,sum(case when avg_salary >= 100 and avg_salary < 200 then 1 else 0 end) as 'B人数'
,sum(case when avg_salary >= 200 and avg_salary < 300 then 1 else 0 end) as 'C人数'
,sum(case when avg_salary >= 300 then 1 else 0 end) as 'D人数'
from
(
select
month
,user_no
,round(avg(salary),2) as avg_salary
from t_salary
group by user_no,month
) as salary_by_user
group by month
输出示例
年月 A人数 B人数 C人数 D人数
2024-01 0 1 0 0
2024-02 0 1 1 0
2024-03 0 0 0 1
问题三
编写SQL查询每个月每个部门的信息和发了工资的人数和总金额，结果格式如下
SQL实现
select
t.`month` as '年月'
,dep.dep_no as '部门编号'
,dep.dep_name as '部门名称'
,dep.user_count as '部门人数'
,t.sum_emp as '发放工资的人数'
,t.sum_salary as '工资总金额'
from t_department dep
left join
(
select
month
,dep_no
,count(user_no) as sum_emp
,sum(salary) as sum_salary
from t_salary
where salary > 0
group by month,dep_no
) as t
on dep.dep_no = t.dep_no
order by t.`month`,dep.dep_no
输出示例
年月 部门编号 部门名称 部门人数 发放工资的人数 工资总金额
2024-01 101 市场部 4 1 100.00
2024-02 101 市场部 4 2 340.00
2024-03 101 市场部 4 1 500.00
问题四
编写SQL查询本月有发放工资但是上个月没有发放过工资的人的信息
SQL实现
SELECT
ts.user_no AS 用户编号,
ts.user_name AS 姓名,
td.dep_name AS 部门名称
FROM t_salary ts
LEFT JOIN t_department td ON ts.dep_no = td.dep_no
WHERE
ts.month = '2024-02' -- 本月
AND NOT EXISTS (
SELECT 1
FROM t_salary ts_prev
WHERE ts_prev.user_no = ts.user_no
AND ts_prev.month = '2024-01' -- 上月
);
输出示例
用户编号 姓名 部门名称
002 小李 市场部
问题五
编写SQL查询历史所有工资数据中存在过连续三个月都是工资最高的人的用户信息
SQL实现
select
user_no
,user_name
,dep_no
from
(
-- 每月工资最高，员工id、员工名称、部门名称、月份、工资
select
user_no
,user_name
,dep_no
,month
,salary
,lag(user_no,1) over (order by month) as lag_user_no -- 用于筛选三个月工资是否相等
,lead(user_no,1) over (order by month) as lead_user_no -- 每月工资最高，员工id、员工名称、工资
from t_salary
where
salary in
(
-- 每月最高的工资
select
max(salary)
from t_salary
group by month
)
order by month
) as t
where t.user_no = t.lag_user_no and t.user_no = t.lead_user_no
输出示例
用户编号 姓名 部门名称
001 小王 101
