默认网站停止,wordpress新建导航,html5小游戏源码,安阳360网站推广工具把一张表 想象成两张表#xff0c;进行多表查询
举例#xff1a;
查询 所有员工的 姓名 以及 其 上级姓名
select s1.stname, s2.stname from staff as s1, staff as s2 where s1.stmgr s2.stid;查询 员工李岩的 上级姓名
select s1.stname, s2.stname from staff as s1…把一张表 想象成两张表进行多表查询
举例
查询 所有员工的 姓名 以及 其 上级姓名
select s1.stname, s2.stname from staff as s1, staff as s2 where s1.stmgr s2.stid;查询 员工李岩的 上级姓名
select s1.stname, s2.stname from staff as s1, staff as s2 where s1.stmgr s2.stid and s1.stname李岩;以上是 利用表的多表查询 还可以使用子查询 来实现 比如 select staff.stname from staff where stid (select stmgr from staff where stname李岩);复杂一些的表查询举例 A 有两张表分别是员工信息表与部门信息表要求查询各个部门工资最高的员工的信息 员工表
mysql select * from employee;
--------------------------
| id | name | salary | depid |
--------------------------
| 1 | Joe | 70000 | 1 |
| 2 | Tom | 80000 | 1 |
| 3 | Mary | 50000 | 2 |
| 4 | Tk | 10000 | 3 |
| 5 | Inter | 20000 | 3 |
| 6 | Janet | 780000 | 3 |
| 7 | Li | 75000 | 1 |
| 8 | Wang | 2000 | 3 |
| 9 | Gao | 5000 | 2 |
| 10 | ZhaoF | 1000 | 2 |
| 11 | ZhaoX | 2000 | 2 |
| 12 | Mx | 5000 | 1 |
| 13 | Mi | 6000 | 1 |
--------------------------
部门表
mysql select * from department;
------------
| id | name |
------------
| 1 | it |
| 2 | kuaiji |
| 3 | yunwei |
------------思考步骤 1、从employee表里查询出每个部门的最高薪资作为一张临时表 t
mysql select depid , max(salary) as maxsalary from employee group by depid;
------------------
| depid | maxsalary |
------------------
| 1 | 80000 |
| 2 | 50000 |
| 3 | 780000 |
------------------
2、将临时表 t 和 employee表进行内连接并新增一列显示employee表里每个员工所在部门对应的最高薪资
mysql select e.id,e.name,e.salary, t.maxsalary,t.depid from (select depid, max(salary) as maxsalary from employee group by depid) as t inner join employee as e on t.depide.depid;
-------------------------------------
| id | name | salary | maxsalary | depid |
-------------------------------------
| 1 | Joe | 70000 | 80000 | 1 |
| 2 | Tom | 80000 | 80000 | 1 |
| 3 | Mary | 50000 | 50000 | 2 |
| 4 | Tk | 10000 | 780000 | 3 |
| 5 | Inter | 20000 | 780000 | 3 |
| 6 | Janet | 780000 | 780000 | 3 |
| 7 | Li | 75000 | 80000 | 1 |
| 8 | Wang | 2000 | 780000 | 3 |
| 9 | Gao | 5000 | 50000 | 2 |
| 10 | ZhaoF | 1000 | 50000 | 2 |
| 11 | ZhaoX | 2000 | 50000 | 2 |
| 12 | Mx | 5000 | 80000 | 1 |
| 13 | Mi | 6000 | 80000 | 1 |
-------------------------------------
3、再用 employee表里每个员工的薪资字段salary 和 部门最高薪资字段列maxsalary进行判断查询出相等数据此处则查询出了每个部门最高薪资的员工有哪些作为表 tt
mysql select e.id,e.name,e.salary, t.maxsalary,t.depid from (select depid, max(salary) as maxsalary from employee group by depid) as t inner join employee as e on t.depide.depid where e.salaryt.maxsalary;
-------------------------------------
| id | name | salary | maxsalary | depid |
-------------------------------------
| 2 | Tom | 80000 | 80000 | 1 |
| 3 | Mary | 50000 | 50000 | 2 |
| 6 | Janet | 780000 | 780000 | 3 |
-------------------------------------
4、因为表 tt 没有部门名称所以我们再将表 tt 和department 表进行内链接查询部门id相等的数据从而查询出每个员工所在的部门名称
mysql select tt.*, d.name as departname from (select e.id,e.name,e.salary, t.maxsalary,t.depid as tepid from (select depid, max(salary) as maxsalary from employee group by depid) as t inner join employee as e on t.depide.depid where e.salaryt.maxsalary) as tt inner join department as d on tt.tepidd.id order by tt.tepid, tt.id;
-------------------------------------------------
| id | name | salary | maxsalary | tepid | departname |
-------------------------------------------------
| 2 | Tom | 80000 | 80000 | 1 | it |
| 3 | Mary | 50000 | 50000 | 2 | kuaiji |
| 6 | Janet | 780000 | 780000 | 3 | yunwei |
-------------------------------------------------
B: 查询 那些 比本部门 平均工资 高的员工的信息
mysql select ss.*, d.dname from (select s.stname, s.stid, s.stsal, s.stdepno, t.avgsal from (select stdepno, avg(stsal) as avgsal from staff group by stdepno) as t inner join staff as s on s.stdepnot.stdepno where s.stsal t.avgsal ) as ss inner join department as d on ss.stdepno d.deptno;另外一种写法
mysql select tt.*, d.dname from (select s.stid, s.stname,s.stsal,temp.avgsal, s.stdepno from (select stdepno, avg(stsal) as avgsal from staff group by stdepno) as temp , staff as s where temp.stdepnos.stdepno and s.stsal temp.avgsal) as tt , department as d where tt.stdepno d.deptno;还有另一种简洁的写法
select s1.* from staff as s1 where s1.stsal (select avg(stsal) from staff as s2 where s2.stdepno s1.stdepno );
