已知2张基本表：部门表：dept （部门号，部门名称）;员工表 emp（员工号，员工姓名，年龄，入职时间，收入，部门号）
1：dept表中有4条记录：
部门号(dept1) 部门名称(dept_name )
101 财务
102 销售
103 IT技术
104 行政
2：emp表中有6条记录：
员工号 员工姓名 年龄 入职时间 收入 部门号对应字段名称为： (sid name age worktime_start incoming dept2)
1789 张三 35 1980/1/1 4000 101
1674 李四 32 1983/4/1 3500 101
1776 王五 24 1990/7/1 2000 101
1568 赵六 57 1970/10/11 7500 102
1564 荣七 64 1963/10/11 8500 102
1879 牛八 55 1971/10/20 7300 103
1.列出每个部门的平均收入及部门名称;
结果：
emp： avg(incoming) ，dept_name
dept: dept_name group by
条件：group by dept_name
方法1：【有链接+条件（分组）】
select dept_name,avg(incoming) from dept right join emp on dept.dept1=emp.dept2 group by dept_name ;
2.财务部门的收入总和；
结果：
emp： sum（incoming）
dept ：dept_name='财务'
方法1：select sum(incoming ) from dept left join emp on dept.dept1=emp.dept2 where dept_name="财务" ;
方法2: select sum(incoming) from emp where dept2=(select dept1 from dept where dept_name='财务' );
方法3：select sum(incoming) from (select * from dept right join emp on dept.dept1=emp.dept2 ) as a where dept_name='财务'
3.It技术部入职员工的员工号
结果：emp ：sid
条件：dept dept_name='IT 技术部门’
方法1：select sid from dept inner join emp on dept.dept1=emp.dept2 where dept_name="IT技术";
方法2：select sid from emp where dept2=(select dept1 from dept where dept_name='IT技术' ) ;
4.财务部门收入超过2000元的员工姓名
结果：name
条件：dept ： dept_name='财务' incoming>2000
方法1：select name from dept inner join emp on dept.dept1=emp.dept2 where dept_name="财务" and incoming>2000;
方法2：select name from emp where incoming>2000 and dept2=(select dept1 from dept where dept_name='财务')
方法3：select a.name from (SELECT * from dept INNER JOIN emp on dept.dept1=emp.dept2 where dept_name='财务' )as a where a.incoming>2000
5.找出销售部收入最低的员工的入职时间；
结果：worktime_start
条件：min(incoming) ，dept_name='销售'
方法1：SELECT woektime_start from dept left join emp on dept.dept1=emp.dept2 where dept_name="销售" and incoming=(SELECT min(incoming) from dept inner join emp on dept.dept1=emp.dept2 and dept2=(select dept1 from dept where dept_name='销售'))
方法2：
select worktime_start from emp where (dept2,incoming)in(select dept2,min(incoming) from emp where dept2=(select dept1 from dept where dept_name="销售"));
6.找出年龄小于平均年龄的员工的姓名，ID和部门名称（所有员工平均年龄）
结果： name sid dept_name
条件 ：avg(age) , age
方法1：select name,sid,dept_name from dept right join emp on dept1=dept2 where age< (select avg(age) from emp);
方法2：select name,dept_name,age from emp LEFT JOIN dept on emp.dept2=dept.dept1 where age<(SELECT avg(age) from dept INNER JOIN emp on dept.dept1=emp.dept2 )
7.列出每个部门收入总和高于9000的部门名称
结果：dept_name
条件： sum（incoming）>9000 ，group by dept_name
方法1：select dept_name from emp INNER JOIN dept on emp.dept2=dept.dept1 group by dept_name having sum(incoming)>9000;
方法2：select a.dept_name from (select dept_name,sum(incoming) from dept LEFT JOIN emp on dept.dept1=emp.dept2 GROUP BY dept_name HAVING sum(incoming)>9000)a;
方法3：select dept_name from (select sum(incoming) sum1,dept_name from dept left join emp on dept.dept1=emp.dept2 group by dept_name)s where s.sum1>9000;
8.查出财务部门工资少于3800元的员工姓名
结果： name
条件：dept_name =' 财务' incoming<3800
方法1：select name,dept_name from dept inner join emp on dept.dept1=emp.dept2 where dept_name="财务" and incoming<3800;
方法2：select name from emp where dept2 =(select dept1 from dept where dept_name='财务') and incoming<3800;
9.求财务部门最低工资的员工姓名；
结果：name
条件：
dept： dept_name='财务'
emp： min（ incoming）
方法1：
select name from dept right join emp on dept.dept1=emp.dept2 where dept_name="财务" and incoming=(select min(incoming) from dept right join emp on dept.dept1=emp.dept2 where dept_name="财务");
方法2 ：（只能显示一个，有多个一样）
select name from emp,dept where dept1=dept2 and dept_name='财务' order by incoming limit 0,1;
方法3：
select name from emp where (incoming,dept2) = (select min(incoming),dept2 from dept join emp on dept1=dept2 group by dept_name having dept_name='财务');
10.找出销售部门中年纪最大的员工的姓名
结果： name
条件： dept_name='销售' max（age）
方法1：
select emp.name from emp left join dept on emp.dept2=dept.dept1 where age=(select max(age) from emp left join dept on emp.dept2=dept.dept1 where dept.dept_name="销售") and dept.dept_name="销售";
方法2：（缺陷：有多个结果，就显示一个）
select name from emp inner join dept on emp.dept2=dept.dept1 where dept_name='销售' ORDER BY age desc limit 1
方法3：
select b.name from (select * from dept join emp on dept1=dept2 where dept_name="销售") b where age=(select max(age) from dept join emp on dept1=dept2 where dept_name="销售");
11.求收入最低的员工姓名及所属部门名称：
结果： name ， dept _name
条件： min（incoming）
方法1：
select name,dept_name from emp left join dept on dept1=emp.dept2
where incoming=(select min(incoming)from emp left join dept on dept.dept1=emp.dept2);
方法2：
SELECT name,dept_name from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE incoming=(SELECT min(incoming)from emp );
12.求李四的收入及部门名称
结果：incoming ，dept_name
条件：name='李四'
方法1：Select incoming,dept_name from dept inner join emp on dept1=dept2 where name='李四';
13.求员工收入小于4000元的员工部门编号及其部门名称
结果： dept2 ，dept_name
条件： incoming<4000
方法1：SELECT dept1,dept_name from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE incoming<4000;
方法2：
SELECT dept_name,dept1 from dept WHERE dept1 in (select dept2 from emp where incoming<4000) ;
14.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序；
方法1：先求每个部门名称和最高工资，在使用合表区匹配（部门名称和薪资一样的员工）
select name,dept_name,incoming from emp join dept on dept1=dept2 where (dept2,incoming) in (select dept2,max(incoming) incoming from emp group by dept2) order by incoming desc;
方法2：
select name,incoming,dept_name from dept INNER JOIN
(SELECT emp.name,emp.incoming,emp.dept2 from emp RIGHT JOIN
(select MAX(incoming) as incoming ,dept2 from emp group by dept2)c
on emp.dept2=c.dept2 and emp.incoming=c.incoming)b
On dept.dept1=b.dept2 ORDER BY incoming DESC;
方法3：
select b.name,b.incoming,b.dept_name from (select MAX(incoming) as incoming ,dept2 from emp group by dept2)a INNER JOIN (select * from dept INNER JOIN emp where dept1=dept2 )b on a.incoming=b.incoming and a.dept2=b.dept2 ORDER BY b.incoming desc ;
方法4：（有缺陷的方法）先降序排出最高工资的人员，在分组，在派排序
select name,dept_name,incoming from (select *from dept a inner join emp b ON
a.dept1=b.dept2 ORDER BY incoming desc )s group by s.dept_name order by incoming
desc
15.求出财务部门收益最高的俩位员工的姓名，工号，收益
结果： name ， sid ，incoming
条件：dept_name='财务' limit 0,2
方法1：
SELECT name,sid,incoming from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE dept_name="财务" ORDER BY incoming desc limit 0,2 ;
方法2：
select name,sid,incoming from (select * from emp join dept on dept1=dept2 where dept_name='财务' order by incoming desc )as a limit 0,2;
方法3：
SELECT NAME,sid,incoming from emp WHERE dept2=(SELECT dept1 FROM dept WHERE dept_name='财务') ORDER BY incoming desc LIMIT 2;
16.查询财务部低于平均收入的员工号与员工姓名：（全部员工平均收入）
结果： sid ，name
条件：dept_name='财务' avg（incoming）
方法1：
select sid,name from dept inner join emp on dept.dept1=emp.dept2 where incoming<(select avg(incoming)from dept right join emp on dept.dept1=emp.dept2) and dept_name="财务";
方法2：
select sid,name from (select * from dept LEFT JOIN emp on dept.dept1=emp.dept2 WHERE dept_name="财务")q where q.incoming<(select avg(incoming) from emp);
方法3：
SELECT sid,name,incoming from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE dept_name="财务" and incoming<(select avg(incoming) from emp);
方法4：
select name,sid from dept INNER JOIN emp on dept.dept1=emp1.dept2 where incoming<(select avg(incoming)from emp1 where dept2=(select dept1 from dept where dept_name="财务"));
17.列出部门员工数大于1个的部门名称；
结果： dept_name
条件： count（name）>1 ，group by dept_name
方法1：select dept_name from emp INNER JOIN dept on dept1=dept2 GROUP BY dept_name HAVING count(name)>1;
方法2：select dept_name from dept where dept1 in (select dept2 from emp group by dept2 having count(dept2)>1);
方法3：SELECT dept_name FROM (select dept_name,count(dept_name) c from dept INNER JOIN emp ON dept.dept1=emp.dept2 GROUP BY dept_name) a WHERE a.c>1;
方法4：select dept_name from dept inner join (SELECT count(dept2),dept2 from emp GROUP BY dept2 having count(dept2)>1)as a on dept.dept1=a.dept2
18.列出部门员工收入不超过7500，且大于3000的员工年纪及部门编号；
结果：age ， dept2 或dept1
条件：incoming<=7500 ,incoming>3000
方法1：select age,dept2,incoming from emp where incoming<=7500 and incoming>3000; （单表）
方法2：select age,sid from dept left join emp on dept.dept1=emp.dept2 where incoming<=7000&&incoming>3000 ;
19.求入职于20世纪70年代的员工所属部门名称；
结果： dept_name
条件：woek_time like 197%
方法1：SELECT dept_name from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE woektime_start BETWEEN 1970 and 1979;
方法2：select dept_name from dept inner join emp on dept.dept1=emp.dept2 where woektime_start>=1970 and woektime_start<1980;
方法3：
select dept_name from dept left join emp on dept.dept1=emp.dept2 where woektime_start like "197%";
20.查找张三所在的部门名称；
结果：dept_name
条件：name='张三'
select dept_name from dept left join emp on dept.dept1=emp.dept2 where name="张三"
方法2：
select dept_name from dept where dept1=(select dept2 from emp where name='张三') ;
21.列出每一个部门中年纪最大的员工姓名，部门名称；
结果： name ，dept_name
条件： group by dept_name max（age）
方法1：SELECT dept_name,name from dept INNER JOIN emp on dept.dept1=emp.dept2 where (dept_name,age ) in (SELECT dept_name ,max(age) from dept INNER JOIN emp on dept.dept1=emp.dept2 GROUP BY dept_name);
方法2：select b.name,b.dept_name from (select MAX(age) as age ,dept2 from emp group by dept2)a INNER JOIN (select * from dept INNER JOIN emp where dept1=dept2 )b on a.age=b.age and a.dept2=b.dept2 ;
22.列出每一个部门的员工总收入及部门名称；
结果：dept_name , sum(incomig)
条件：group by dept_name ,
方法1：select sum(incoming),dept_name from dept left join emp on dept.dept1=emp.dept2 group by dept_name;
23.列出部门员工收入大于7000的员工号，部门名称；
结果：sid ，dept_name
条件：incoming>7000
方法1：SELECT sid,dept_name from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE incoming>7000;
24.找出哪个部门还没有员工入职；（左独有数据）
结果： dept_name
条件：name is null
方法1：SELECT dept_name from dept LEFT JOIN emp on dept.dept1=emp.dept2 WHERE name is null;
方法2：select dept_name from dept left join emp on dept.dept1=emp.dept2 group by dept_name having (count(sid)=0);
25.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表 ；（二次排序）
结果：员工信息表 所有信息 *
emp ：dept2 order by desc （降序）
emp ： woek_time 时间是越小就越早(升序)
方法1：select * from dept left join emp on dept.dept1=emp.dept2 order by dept1 desc ,worktime_start asc;
方法2：select *from emp order by dept2 desc,woektime_start asc;
26.求出财务部门工资最高员工的姓名和员工号
结果：name ，sid
条件：max（incomg） ，dept_name='财务'
方法1：
select sid,name from dept left join emp on dept.dept1=emp.dept2 where dept_name="财务" and incoming=(select max(incoming) from dept left join emp on dept.dept1=emp.dept2 where dept_name="财务");
27.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称。
结果：name，dept_name
条件： incoming=< 7500, incoming>=8000 max（age）
方法1：SELECT name,dept_name FROM dept right JOIN emp on dept.dept1=emp.dept2 WHERE age=(SELECT max(age) from emp WHERE incoming BETWEEN 7500 and 8500) and incoming BETWEEN 7500 and 8500 ;、
方法2：
select name,dept_name from dept join emp on dept1=dept2 where incoming between 7500 and 8500 and (dept_name,age) in (select dept_name,max(age) from emp join dept on dept1=dept2 group by dept_name);
标签：多表,name,10.27,dept2,dept,emp,incoming,select From： https://www.cnblogs.com/huhao20001007/p/18513981