每天五道MySQL---4

亦凉 2021-09-30 12:14 264阅读 0赞

1.  获取当前（to\_date='9999-01-01'）薪水第二多的员工的emp\_no以及其对应的薪水salary
    
        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`)); --------------------------------- select emp_no, salary from salaries where to_date='9999-01-01' order by salary desc limit 1 offset 1
2.  查找当前薪水(to\_date='9999-01-01')排名第二多的员工编号emp\_no、薪水salary、last\_name以及first\_name，不准使用order by
    
        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`)); ---------------------------------- select s.emp_no,Max(s.salary),e.last_name,e.first_name from employees e join salaries s on e.emp_no = s.emp_no where s.to_date='9999-01-01' and s.salary not in ( select Max(salary) from salaries where s.to_date='9999-01-01' )
3.  查找所有员工的last\_name和first\_name以及对应的dept\_name，也包括暂时没有分配部门的员工
    
        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`)); ------------------------------------ select e.last_name,e.first_name,de.dept_name from employees e left join dept_emp d on d.emp_no = e.emp_no left join departments de on de.dept_no = d.dept_no
4.  查找员工编号emp\_no为10001其自入职以来的薪水salary涨幅值growth
    
        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`)); --------------------------------- select max(salary) - min(salary) as growth from salaries where emp_no = 10001
5.  查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp\_no以及其对应的薪水涨幅growth，并按照growth进行升序
    
        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`)); --------------------------------- select now.emp_no,(now.salary-th.salary)growth from (select emp_no, salary from salaries where to_date='9999-01-01') now inner join (select e.emp_no, s.salary from employees e ,salaries s where e.emp_no=s.emp_no and e.hire_date=s.from_date) th where now.emp_no=th.emp_no order by growth

转载于:https://www.cnblogs.com/luo-bo/p/11148138.html