查找入职员工时间排名倒数第三的员工所有信息 MySql select * from employees where hire_date = (select hire_date from employees order by hire_date desc limit 2,1) Oracle select * from emp where hiredate =(select max(hiredate) from (select * from emp where rownum < 4 order by hiredate));
查找各个部门当前(dept_manager.to_date=‘9999-01-01‘)领导当前(salaries.to_date=‘9999-01-01‘)薪水详情以及其对应部门编号dept_no select s.emp_no,s.salary,s.from_date,s.to_date,dm.dept_no from salaries s,dept_manager dm where s.to_date=‘9999-01-01‘ AND dm.to_date=‘9999-01-01‘AND dm.emp_no = s.emp_no; emp_no salary from_date to_date dept_no 10002 72527 2001-08-02 9999-01-01 d001 10004 74057 2001-11-27 9999-01-01 d004 10005 94692 2001-09-09 9999-01-01 d003 10006 43311 2001-08-02 9999-01-01 d002 10010 94409 2001-11-23 9999-01-01 d006
查找所有员工的last_name和first_name以及对应部门编号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`)); 输出结果：
| last_name | first_name | dept_no |
|---|---|---|
| Facello | Georgi | d001 |
| 省略 | 省略 | 省略 |
| Sluis | Mary | NULL(在sqlite中此处为空,MySQL为NULL) |
执行SQL： select e.last_name,e.first_name,d.dept_no from employees e left join dept_emp d on e.emp_no = d.emp_no;
提示：
有null即为外连接啊
查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_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`)); 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`)); sql语句： 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 order by e.emp_no desc;
select e.emp_no,s.salary from employees e join salaries s on e.emp_no = s.emp_no and e.hire_date = s.from_date order by e.emp_no desc;
找出所有员工当前(to_date=‘9999-01-01‘)具体的薪水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 distinct salary from salaries where to_date=‘9999-01-01‘ group by emp_no order by salary desc;
获取所有部门当前(dept_manager.to_date=‘9999-01-01‘)manager的当前(salaries.to_date=‘9999-01-01‘)薪水情况，给出dept_no, emp_no以及salary，输出结果按照dept_no升序排列(请注意，同一个人可能有多条薪水情况记录) CREATE TABLE `dept_manager` ( `dept_no` char(4) NOT NULL, `emp_no` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_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`)); sql语句： select d.dept_no,d.emp_no,s.salary from dept_manager d , salaries s where d.emp_no=s.emp_no and d.to_date=‘9999-01-01‘ and s.to_date=‘9999-01-01‘ order by d.dept_no;
获取所有员工当前的(dept_manager.to_date=‘9999-01-01‘)manager，如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的emp_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 `dept_manager` ( `dept_no` char(4) NOT NULL, -- ‘部门编号‘ `emp_no` int(11) NOT NULL, -- ‘经理编号‘ `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`)); 如插入： INSERT INTO dept_emp VALUES(10001,‘d001‘,‘1986-06-26‘,‘9999-01-01‘); INSERT INTO dept_emp VALUES(10002,‘d001‘,‘1996-08-03‘,‘9999-01-01‘); INSERT INTO dept_emp VALUES(10003,‘d004‘,‘1995-12-03‘,‘9999-01-01‘); INSERT INTO dept_emp VALUES(10004,‘d004‘,‘1986-12-01‘,‘9999-01-01‘); INSERT INTO dept_emp VALUES(10005,‘d003‘,‘1989-09-12‘,‘9999-01-01‘); INSERT INTO dept_emp VALUES(10006,‘d002‘,‘1990-08-05‘,‘9999-01-01‘); INSERT INTO dept_emp VALUES(10007,‘d005‘,‘1989-02-10‘,‘9999-01-01‘); INSERT INTO dept_emp VALUES(10008,‘d005‘,‘1998-03-11‘,‘2000-07-31‘); INSERT INTO dept_emp VALUES(10009,‘d006‘,‘1985-02-18‘,‘9999-01-01‘); INSERT INTO dept_emp VALUES(10010,‘d005‘,‘1996-11-24‘,‘2000-06-26‘); INSERT INTO dept_emp VALUES(10010,‘d006‘,‘2000-06-26‘,‘9999-01-01‘); INSERT INTO dept_manager VALUES(‘d001‘,10002,‘1996-08-03‘,‘9999-01-01‘); INSERT INTO dept_manager VALUES(‘d002‘,10006,‘1990-08-05‘,‘9999-01-01‘); INSERT INTO dept_manager VALUES(‘d003‘,10005,‘1989-09-12‘,‘9999-01-01‘); INSERT INTO dept_manager VALUES(‘d004‘,10004,‘1986-12-01‘,‘9999-01-01‘); INSERT INTO dept_manager VALUES(‘d005‘,10010,‘1996-11-24‘,‘2000-06-26‘); INSERT INTO dept_manager VALUES(‘d006‘,10010,‘2000-06-26‘,‘9999-01-01‘); sql语句： emp_no,m.emp_no as manager_no from dept_emp e,dept_manager m
where m.to_date=‘9999-01-01‘ and e.to_date = ‘9999-01-01‘ and e.dept_no=m.dept_no and e.emp_no <> m.emp_no
获取所有部门中当前(dept_emp.to_date = ‘9999-01-01‘)员工当前(salaries.to_date=‘9999-01-01‘)薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列。 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 `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`)); sql语句： select d.dept_no,d.emp_no,max(s.salary) from dept_emp d inner join salaries s on d.emp_no=s.emp_no and d.to_date=‘9999-01-01‘ and s.to_date = ‘9999-01-01‘ group by d.dept_no order by dept_no;
从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。 注意对于重复的emp_no进行忽略(即emp_no重复的title不计算，title对应的数目t不增加)。 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);
从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。 注意对于重复的emp_no进行忽略(即emp_no重复的title不计算，title对应的数目t不增加)。 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); sql语句：
先给emp_no过滤下，意思要先查询emp_no，title然后再分组查询 select a.title,count(title) t from (select distinct(emp_no) ,title from titles) a group by a.title having t >= 2;
更简单的写法是先分组，由于要给emp_no去重，可以只是统计分组里的emp_no就行了 select title,count(distinct(emp_no)) t from titles group by title having t >= 2;
查找employees表所有emp_no为奇数，且last_name不为Mary(注意大小写)的员工信息，并按照hire_date逆序排列 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`)); Sql语句： select * from employees where mod(emp_no,2)=1 and last_name != ‘Mary‘ order by hire_date desc; SELECT * FROM employees WHERE emp_no%2 =1 AND last_name !=‘Mary‘ ORDER BY hire_date Desc
统计出当前(titles.to_date=‘9999-01-01‘)各个title类型对应的员工当前(salaries.to_date=‘9999-01-01‘)薪水对应的平均工资。结果给出title以及平均工资avg。 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); sql语句： select t.title,avg(s.salary) from titles t , salaries s where t.to_date=‘9999-01-01‘ and s.to_date=‘9999-01-01‘ and t.emp_no=s.emp_no group by title;
获取当前（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,1;
查找当前薪水(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`)); sql语句 select e.emp_no,max(s.salary),e.last_name,e.first_name from employees e, salaries s where e.emp_no = s.emp_no and s.to_date=‘9999-01-01‘ and salary <> (select max(salary) from salaries ) 通过查看工资第N高 select e.emp_no,salary,last_name,first_name from employees e,salaries s where e.emp_no = s.emp_no and s.to_date=‘9999-01-01‘ and salary =( select s1.salary from salaries s1,salaries s2 where s1.salary<=s2.salary group by s1.salary having count(distinct s2.salary)=2 );
查找所有员工的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`)); sql语句： select e.last_name,e.first_name,d.dept_name from employees as e left join dept_emp as de on e.emp_no=de.emp_no left join departments as d on de.dept_no=d.dept_no;
查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
（注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的to_date!=‘9999-01-01‘，这样的数据不显示在查找结果里面）
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,s.salary FROM employees e,salaries s WHERE e.emp_no = s.emp_no AND s.from_date = e.hire_date -- 当前薪水 SELECT s.emp_no,s.salary FROM employees e,salaries s WHERE e.emp_no = s.emp_no AND s.to_date = ‘9999-01-01‘
sql语句：
select sCurrent.emp_no,(sCurrent.salary-sStart.salary) growth from
(select e.emp_no,s.salary from employees e,salaries s where e.hire_date=s.from_date and e.emp_no=s.emp_no) as sStart ,
(select e.emp_no,s.salary from employees e,salaries s where e.emp_no=s.emp_no and s.to_date=‘9999-01-01‘) as sCurrent
where sStart.emp_no = sCurrent.emp_no order by growth；
统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，按照dept_no升序排序 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 `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`)); sql语句： select dp.dept_no,ds.dept_name,count(ss.salary) from dept_emp dp inner join departments ds on dp.dept_no = ds.dept_no inner join salaries ss on ss.emp_no = dp.emp_no group by dp.dept_no,ds.dept_name;
对所有员工的当前(to_date=‘9999-01-01‘)薪水按照salary进行按照1-N的排名，相同salary并列且按照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 emp_no,salary,dense_rank() over(order by salary desc) as rank from salaries where to_date=‘9999-01-01‘ order by rank asc,emp_no asc; 输出：
| emp_no | salary | t_rank |
|---|---|---|
| 10005 | 94692 | 1 |
| 10009 | 94409 | 2 |
| 10010 | 94409 | 2 |
| 10001 | 88958 | 3 |
| 10007 | 88070 | 4 |
| 10004 | 74057 | 5 |
| 10002 | 72527 | 6 |
| 10003 | 43311 | 7 |
| 10006 | 43311 | 7 |
| 10011 | 25828 | 8 |
获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date=‘9999-01-01‘, 结果第一列给出员工的emp_no， 第二列给出其manager的manager_no， 第三列给出该员工当前的薪水emp_salary, 第四列给该员工对应的manager当前的薪水manager_salary 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 `dept_manager` ( `dept_no` char(4) NOT NULL, `emp_no` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_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 de.emp_no,ss.salary,de.dept_no from dept_emp de inner join salaries ss on de.emp_no=ss.emp_no and ss.to_date = ‘9999-01-01‘ -- 上级当前薪水、信息 select dm.emp_no,ss.salary,dm.dept_no from dept_manager dm inner join salaries ss on dm.emp_no=ss.emp_no and ss.to_date = ‘9999-01-01‘ sql语句： select clerk.emp_no as emp_no, manager.emp_no as manager_no, clerk.salary as emp_salary, manager.salary as manager_salary from (select de.emp_no,ss.salary,de.dept_no from dept_emp de inner join salaries ss on de.emp_no=ss.emp_no and ss.to_date = ‘9999-01-01‘) as clerk, (select dm.emp_no,ss.salary,dm.dept_no from dept_manager dm inner join salaries ss on dm.emp_no=ss.emp_no and ss.to_date = ‘9999-01-01‘) as manager where clerk.salary > manager.salary and clerk.dept_no=manager.dept_no;