多表查询练习及解析 - 黑马程序员技术交流社区

[SQL] 纯文本查看 复制代码
--1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。

--多表连查，多行多列

SELECT d.deptno, d.dname, d.loc, e.c AS count 

FROM dept AS d, (SELECT deptno, count(*) AS c FROM emp GROUP BY deptno) AS e 

WHERE d.deptno=e.deptno;



--3. 列出所有员工的姓名及其直接上级的姓名。

--多表查询，多行多列

SELECT e.ename, m.ename AS mname FROM emp AS e, (SELECT empno, ename FROM emp) AS m WHERE e.mgr=m.empno;

--以上有bug，没有显示老板，老板的mgr是NULL加方法即可IFNULL()但是依然没有这个项

--连接查询，左连接没有强调INNER还是OUTER

SELECT e.ename, IFNULL(m.ename, 'BOSS') AS lead

FROM emp e LEFT JOIN emp m

ON e.mgr=m.empno;



/*4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。



员工的编号emp.empno、姓名emp.ename、部门名称dept.dname

表:员工emp，上级emp, dept

利用子查询只能得到一半结果，下面就无法进行了，如下

SELECT e.empno, e.ename FROM emp AS e WHERE hiredate < (SELECT hiredate FROM emp AS m WHERE e.mgr=m.empno);

子查询不可以多层嵌套，建议用连接查询，可以多连接，可以得到新表之后再通过日期筛选

单嵌套，多连接，先多表查再筛选

*/

SELECT e.empno,e.ename, m.ename, d.dname 

FROM emp e 

LEFT JOIN emp m ON e.mgr=m.empno 

LEFT JOIN dept d ON d.deptno=m.deptno

WHERE e.hiredate<m.hiredate;



/*5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

字段：部分名称（可重复）dept.dname，员工信息（可以为NULL）emp.*

dept为主表，emp为副表，两种写法如下

*/



SELECT d.dname,e.* FROM dept d 

LEFT JOIN emp e ON e.deptno=d.deptno;



SELECT e.*, d.dname FROM emp e 

RIGHT JOIN dept d ON e.deptno=d.deptno;



/*7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。

这个主要是对分组条件的理解

WHERE        在分组之前，连接表之后，或者子查询嵌套时筛选

HAVING        在分组之后筛选

ON                是连接表的时候筛选



*/

SELECT job, COUNT(*)

FROM emp e

GROUP BY job

HAVING MIN(sal) > 15000;



/*8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。

两个方法：连接查询 和 子查询（单行单列）

*/

SELECT e.ename FROM emp e 

JOIN dept d ON e.deptno=d.deptno 

WHERE d.dname='销售部';



SELECT ename FROM emp WHERE deptno=

(SELECT * FROM dept WHERE dname='销售部');





/*9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。

第一段是我写的，第二段是标准答案。原来薪金不包括奖金啊。

还有NATRUAL的作用不太清楚（在这里是自然左外连接）

*/

SELECT e.*,d.dname,IFNULL(m.ename,'BOSS') lead,g.grade  FROM emp e 

LEFT JOIN dept d ON d.deptno=e.deptno 

LEFT JOIN emp m ON m.empno=e.mgr

LEFT JOIN salgrade g ON e.sal BETWEEN g.losal AND g.hisal

WHERE e.sal+IFNULL(e.comm,0) > (SELECT AVG(sal+IFNULL(comm,0)) FROM emp);



SELECT e.*, d.dname, m.ename, s.grade

FROM emp e 

  NATURAL LEFT JOIN dept d

  LEFT JOIN emp m ON m.empno=e.mgr

  LEFT JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal

WHERE e.sal > (SELECT AVG(sal) FROM emp);



/*10.列出与庞统从事相同工作的所有员工及部门名称。

第一段是我写的，第二段是标准答案。

连接查询我用的是自然内连接，答案用的是内连接的mysql方言。

*/

SELECT e.ename, d.dname FROM emp e

NATURAL JOIN dept d

WHERE job=(SELECT job FROM emp WHERE ename='庞统');



SELECT e.*, d.dname

FROM emp e, dept d

WHERE e.deptno=d.deptno AND e.job=(SELECT job FROM emp WHERE ename='庞统');



/*11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。

第一段是我写的，第二段是标准答案。

连接查询：我的是左连接(好像不用分内外还是左右)，答案是msql方言内连接而且两个WHERE条件用AND合在一起了

sal条件不用：我的是sal>他们的最大工资，答案是用ALL集合表示大于他们每一位的工资。

IN ALL ANY在子查询多汗单列中的用法与区别

field IN(SELECT field ...)匹配集合中的某一元素

field > ALL(SELECT field ...) field大于集合中任意一个元素(相当于大于集合中最大的元素,如果是<则小于最小的)

field > ANY(SELECT field ...) field大于任何一个都可以(相当于大于最小的就满足条件)

field = ANY(SELECT field ...) 与 field IN(SELECT field ...) 等价

注：

IN可以自定义集合比如：IN(a,b,c,d)而另外俩个不可以。

IN可以应用与子查询是单行多列的查询，比如：(field1,field2, ...) IN (SELECT field1,field2, ...)



*/

SELECT e.ename, e.sal, d.dname FROM emp e 

LEFT JOIN dept d ON e.deptno=d.deptno

WHERE sal>(SELECT MAX(sal) FROM emp,dept WHERE emp.deptno=dept.deptno AND emp.deptno=30);



SELECT e.ename, e.sal, d.dname

FROM emp e, dept d

WHERE e.deptno=d.deptno AND sal > ALL(SELECT sal FROM emp WHERE deptno=30)



/*13.查出年份、利润、年度增长比



*/



/*2. 列出薪金比关羽高的所有员工。

子查询（单行单列）求出关羽的sal：SELECT sal FROM emp WHERE ename='关羽';

SELECT * FROM emp WHERE sal>(关羽的sal);

*/

SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='关羽');



/*6. 列出所有文员的姓名及其部门名称，部门的人数。

第一段是我写的，第二段是标准答案。

*. 部门人数作为子表，可以放到FROM后，也可以放到JOIN后（证明我的方法的正确性）

*. 两个JOIN副表都是加入到主表emp中，所以ON条件都是和emp作比较

*. c表的COUNT(*)的别名问题，count与关键字“COUNT”重复，建议别名不要用关键字

*. 左表emp不存在不满足右表dept的记录，所以用内连接即可，可以用msql方言ON条件放到WHERE里

我的代码修改如下：

SELECT e.ename, d.dname, c.num FROM emp e, dept d

LEFT JOIN (SELECT deptno,COUNT(*) num FROM emp GROUP BY deptno) c ON c.deptno=d.deptno

WHERE e.job='文员' AND e.deptno=d.deptno;

*. 左表dept不存在不满足右表(子表部门人数表)的记录,所以用内连接即可，可以用mysql方言ON条件放到WHERE里

再次修改我的代码：

SELECT e.ename, d.dname, c.num 

FROM emp e, dept d, (SELECT deptno,COUNT(*) num FROM emp GROUP BY deptno) c 

WHERE e.job='文员' AND d.deptno=e.deptno AND c.deptno=e.deptno;

两个AND后的条件e,d,c没有顺序要求，什么顺序都可以，比如：

e.deptno=d.deptno AND e.deptno=c.deptno

d.deptno=c.deptno AND d..deptno=e.deptno

建议安装容易理解的逻辑顺序去写，例如：（d表c表都加入e表）

d.deptno=e.deptno AND c.deptno=e.deptno



*/



 SELECT e.ename, d.dname, c.count FROM emp e 

 LEFT JOIN dept d ON d.deptno=e.deptno

 LEFT JOIN (SELECT deptno,COUNT(*) count FROM emp GROUP BY deptno) c ON c.deptno=e.deptno

 WHERE e.job='文员';

 

SELECT e.ename, d.dname, z.cnt

FROM emp e, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z, dept d

WHERE e.deptno=d.deptno AND z.deptno=d.deptno;

/*12.列出每个部门的员工数量、平均工资。

前两个是我写的，最后一个是答案。

我写的，表明上看是分组和连接一块儿做，实际上是先连接后分组。而且WHERE条件要写在GROUP BY分组的前面

*/

SELECT dept.dname,COUNT(*) dnum,AVG(sal) dsal FROM emp 

JOIN dept ON dept.deptno=emp.deptno

GROUP BY dept.deptno;



SELECT dept.dname,COUNT(*) dnum,AVG(sal) dsal FROM emp,dept 

WHERE dept.deptno=emp.deptno

GROUP BY dept.deptno;



SELECT d.dname, e.cnt, e.avgsal

FROM (SELECT deptno, COUNT(*) cnt, AVG(sal) avgsal FROM emp GROUP BY deptno) e, dept d

WHERE e.deptno=d.deptno;