MySQL代码子查询续集

 dept表：

 

 emp表：

-- 查询每个部门的信息(包括：部门名，编号，地址)和人员数量
-- 1.将两张表结合起来，筛选出部门名，编号，地址 

tips:     

1.   表.* 表示将该表所有列都显示出来，可以简化sql语句
2.   在多表查询中，当多个表的列不重复时，才可以直接写列名

-- 查询每个部门的信息(包括：部门名，编号，地址)和人员数量
-- 1.将两张表结合起来，筛选出部门名，编号，地址
SELECT DISTINCT dname ,loc, dept.deptno
	FROM dept , emp
	WHERE dept.deptno = emp.deptno
-- 2.将每个部门的人员数量统计出来
SELECT COUNT(*) AS total,deptno
	FROM emp
	GROUP BY deptno;
	
-- 3.将两个查询信息拼接起来
SELECT dname ,loc , dept.deptno, per_total
	FROM dept, (
		SELECT COUNT(*) AS per_total,deptno
		FROM emp
		GROUP BY deptno
	) temp
	WHERE dept.deptno = temp.deptno

SELECT dname, dept.deptno, loc ,tmp.per_num AS '人数'
	FROM dept, (
		SELECT COUNT(*) AS per_num, deptno
		FROM emp
		GROUP BY deptno
	) tmp
	WHERE tmp.deptno = dept.deptno
	
SELECT dname, loc ,tmp.*
	FROM dept, (
		SELECT COUNT(*) AS per_num, deptno
		FROM emp
		GROUP BY deptno
	) tmp
	WHERE tmp.deptno = dept.deptno