SQL练习：轻轻松松，1（中等）+2（较难）

SQL22 统计各个部门的工资记录数

我的思路： 需求搞清楚，就蛮简单了，先将部门员工表和薪水表连接，然后通过部门编号分组，计算数据条数，可以得出每个部门对应的薪水记录数，然后和部门表连起来，即可获取对应的部门名称了。

我的题解：

select a1.dept_no, b1.dept_name, a1.sum
from (
select a.dept_no, count(a.emp_no) as sum
from dept_emp as a
join salaries as b
on a.emp_no = b.emp_no
group by a.dept_no) as a1, departments as b1
where a1.dept_no = b1.dept_no;

涉及知识点：

• 完整sql执行顺序（每天看一遍，不信记不住）：

from -> where -> group by -> having -> select -> order by -> limit

DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (
  `dept_no` varchar(11) NOT NULL,
  `dept_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`dept_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `departments` VALUES ('d001', 'Marketing');
INSERT INTO `departments` VALUES ('d002', 'Finance');


DROP TABLE IF EXISTS `dept_emp`;
CREATE TABLE `dept_emp` (
  `emp_no` int(11) DEFAULT NULL,
  `dept_no` varchar(255) DEFAULT NULL,
  `from_date` date DEFAULT NULL,
  `to_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `dept_emp` VALUES ('10001', 'd001', '2001-06-22', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10002', 'd001', '1996-08-03', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10003', 'd002', '1996-08-03', '9999-01-01');


DROP TABLE IF EXISTS `salaries`;
CREATE TABLE `salaries` (
  `emp_no` int(11) DEFAULT NULL,
  `salary` int(11) DEFAULT NULL,
  `from_date` date DEFAULT NULL,
  `to_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `salaries` VALUES ('10001', '85097', '2001-06-22', '2002-06-22');
INSERT INTO `salaries` VALUES ('10001', '88958', '2002-06-22', '9999-01-01');
INSERT INTO `salaries` VALUES ('10002', '72527', '1996-08-03', '9999-01-01');
INSERT INTO `salaries` VALUES ('10003', '32323', '1996-08-03', '9999-01-01');

https://mp.weixin.qq.com/s/e8FI__8mHJzLyWNHcAMrJA