-- 链接 LINK: SQL220 汇总各个部门当前员工的title类型的分配数目
-- 标题 TITLE: https://www.nowcoder.com/practice/4bcb6a7d3e39423291d2f7bdbbff87f8?tpId=82&tqId=29778&rp=1&ru=/exam/oj&qru=/exam/oj&sourceUrl=%2Fexam%2Foj%3Fdifficulty%3D5%26page%3D1%26pageSize%3D50%26search%3D%26tab%3DSQL%25E7%25AF%2587%26topicId%3D82&difficulty=5&judgeStatus=undefined&tags=&title=
-- 日期 DATE: 2023-02-25

/* 设计思路

**/

-- 题解 SQL
SELECT dept_no, dept_name, title, count
FROM (
    SELECT dept_no, title, COUNT(DISTINCT emp_no) AS 'count'
    FROM (
        SELECT emp_no, dept_no FROM dept_emp
        WHERE to_date = '9999-01-01'
    ) T1
    INNER JOIN (
        SELECT emp_no, title FROM titles
        WHERE to_date = '9999-01-01'
    ) T2 USING (emp_no)
    GROUP BY dept_no, title
) TA
INNER JOIN departments TB USING(dept_no)
ORDER BY dept_no, title


;

-- 数据 DATA ===================================================
drop table if exists  `departments` ; 
drop table if exists  `dept_emp` ; 
drop table if exists  titles ;
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE titles (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);
INSERT INTO departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1995-12-03','9999-01-01');
INSERT INTO titles VALUES(10001,'Senior Engineer','1986-06-26','9999-01-01');
INSERT INTO titles VALUES(10002,'Staff','1996-08-03','9999-01-01');
INSERT INTO titles VALUES(10003,'Senior Engineer','1995-12-03','9999-01-01');