SQL典型例题总结

SQL
SQL

1. 统计出当前各个title类型对应的员工当前薪水对应的平均工资。

表结构：

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

思路：

  • INNER JOIN的使用
  • GROUP BY + AVG( )的使用

代码：

1
2
3
4
5
SELECT titles.title AS title, AVG(salaries.salary) AS avg
FROM titles
INNER JOIN salaries
ON salaries.emp_no = titles.emp_no AND salaries.to_date = titles.to_date AND salaries.to_date = '9999-01-01'
GROUP BY titles.title;

2. 获取当前（to_date=’9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水salary

表结构：

1
2
3
4
5
6
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

思路：

  • distinct 和 limit的使用

代码：

1
2
3
4
5
6
7
-- 第一种解法
SELECT emp_no, max(salary) FROM salaries
WHERE salary < (SELECT MAX(SALARY) FROM salaries) and to_date = '9999-01-01';
--第二种解法
select emp_no, salary from salaries
where to_date = '9999-01-01' and salary = (select distinct salary from salaries order by salary desc limit 1,1)

3. 查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

表结构：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

思路：

  • left join + 三表联合查询

代码：

1
2
3
SELECT employees.last_name, employees.first_name, departments.dept_name 
FROM (employees LEFT JOIN dept_emp ON employees.emp_no = dept_emp.emp_no)
LEFT JOIN departments ON dept_emp.dept_no = departments.dept_no;

4. 查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_noy以及其对应的薪水涨幅growth，并按照growth进行升序

表结构：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

思路：

  • 看清题意~~
  • b表为当前工资，c表为入职工资

代码：

1
2
3
4
5
6
7
8
select a.emp_no, (b.salary - c.salary) as growth
from
    employees as a
    inner join salaries as b
    on a.emp_no = b.emp_no and b.to_date = '9999-01-01'
    inner join salaries as c
    on a.emp_no = c.emp_no and a.hire_date = c.from_date
order by growth asc;

Compartir Comentarios