一、示例數(shù)據(jù)表
1.1 假設(shè)我們有一張示例數(shù)據(jù)表“employees”，包含以下列：
1.2 下面是創(chuàng)建employees表并插入樣本數(shù)據(jù)的MySQL腳本：
create TABLE employees( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, gender VARCHAR(10) NOT NULL, salary INT NOT NULL, department VARCHAR(50) NOT NULL);
insert INTO employees (name, gender, salary, department)VALUES ('Ramesh Gupta', 'Male', 55000, 'Sales'), ('Priya Sharma', 'Female', 65000, 'Marketing'), ('Sanjay Singh', 'Male', 75000, 'Sales'), ('Anjali Verma', 'Female', 45000, 'Finance'), ('Rajesh Sharma', 'Male', 80000, 'Marketing'), ('Smita Patel', 'Female', 60000, 'HR'), ('Vikram Yadav', 'Male', 90000, 'Sales'), ('Neha Sharma', 'Female', 55000, 'Marketing'), ('Rahul Singh', 'Male', 70000, 'Finance'), ('Sonali Gupta', 'Female', 50000, 'Sales');
Employees 表
二、查詢(xún)
2.1 查詢(xún)每個(gè)部門(mén)中男女職工的平均薪水
實(shí)現(xiàn)方案：
select department, gender, AVG(salary) AS avg_salary
from employees
GROUP BY department, gender;
輸出：
2.2 查詢(xún)每個(gè)部門(mén)中薪水最高的員工姓名及薪水
實(shí)現(xiàn)方案：
select name, salary
from employees
where (department, salary) IN (select department, MAX(salary)
from employees
GROUP BY department);
輸出：
2.3 查詢(xún)每個(gè)部門(mén)中比所在部門(mén)平均薪水高的員工信息
實(shí)現(xiàn)方案：
select name, salary, department
from employeeswhere salary > (select AVG(salary)
from employees AS e2
where e2.department = employees.department);
輸出：
2.4 查詢(xún)每個(gè)部門(mén)中薪水前三名的員工信息
實(shí)現(xiàn)方案：
select e.department, e.name, e.salary
from employees e
where (select COUNT(*)
from employees
where department = e.department
AND salary > e.salary) < 3;
輸出：
2.5 查詢(xún)每個(gè)部門(mén)中比所在部門(mén)平均薪水高的員工姓名（該問(wèn)題與第三個(gè)問(wèn)題一樣，只不過(guò)這里使用連接實(shí)現(xiàn)）
實(shí)現(xiàn)方案：
select e.namefrom employees e
JOIN (select department, AVG(salary) AS avg_salary
from employees
GROUP BY department) AS dept_avg
ON e.department = dept_avg.department
where e.salary > dept_avg.avg_salary;
輸出：
2.6 查詢(xún)每個(gè)部門(mén)中最高薪水的員工信息
實(shí)現(xiàn)方案：
WITH max_salary AS (select department, MAX(salary) AS highest_salary
from employees
GROUP BY department)
select m.department, e.name, e.salaryfrom employees e
JOIN max_salary m
ON e.department = m.department AND e.salary = m.highest_salary;
輸出：
該實(shí)現(xiàn)需要MySQL 8.0及以上版本才可運(yùn)行，因?yàn)?code style=";padding: 0px;outline: 0px;user-select: initial !important;max-width: 100%;box-sizing: border-box !important;overflow-wrap: break-word !important">WITH...AS...子句只有8.0及以上版本才支持。
該文章在 2023/11/16 23:02:23 編輯過(guò)
400 186 1886
