今天我们介绍一个优秀员工分析的案例，涉及的知识点是SQL集合运算符。首先，创建一个年度优秀员工表excellent_emp：

CREATE TABLE excellent_emp(    year   INTEGER NOT NULL,    emp_id INTEGER NOT NULL,    emp_name VARCHAR(50) NOTNULL,    CONSTRAINTpk_excellent_emp PRIMARY KEY (year, emp_id)); INSERT INTO excellent_emp VALUES (2019, 9, '赵云');INSERT INTO excellent_emp VALUES (2019, 11, '关平');INSERT INTO excellent_emp VALUES (2020, 9, '赵云');INSERT INTO excellent_emp VALUES (2020, 16, '周仓');

excellent_emp表中记录了每个年度的优秀员工编号和姓名。

如果我们想要查找2019年和2020年都是优秀员工的员工信息，可以使用INTERSECT运算符实现如下：

-- Oracle、MicrosoftSQL Server、PostgreSQL以及SQLiteSELECT emp_id, emp_name -- 2019年优秀员工FROM excellent_empWHERE year = 2019INTERSECTSELECT emp_id, emp_name -- 2020年优秀员工FROM excellent_empWHERE year = 2020;

查询返回的结果如下。

emp_id|emp_name------|--------     9|赵云

查询结果显示，只有“赵云”连续两年获得了优秀员工的称号。

对于MySQL，我们可以使用以下内连接查询实现相同的结果。

SELECT t1.emp_id, t1.emp_nameFROM excellent_emp t1JOIN excellent_emp t2ON (t1.emp_id = t2.emp_id AND t1.year = 2019 AND t2.year = 2020);

以上语句也可以用于其他4种数据库。

如果我们还想知道2019年和2020年的所有优秀员工，可以使用UNION运算符实现：

SELECT emp_id, emp_nameFROM excellent_empWHERE year = 2019UNION ALLSELECT emp_id, emp_nameFROM excellent_empWHERE year = 2020;

查询返回的结果如下。

emp_id|emp_name------|--------     9|赵云         9|赵云        11|关平        16|周仓

我们使用了UNION ALL，因此查询结果中出现了两个“赵云”，说明他在2019年和2020年都获得了优秀员工称号。如果我们将UNIONALL运算符替换成UNION运算符，“赵云”在查询结果中只会出现一次。

接下来，我们想要找出2020年有哪些新晋的优秀员工，他们在2019年没有获得这个称号。我们可以使用EXCEPT运算符实现如下：

-- Microsoft SQL Server、PostgreSQL以及SQLiteSELECT emp_id, emp_nameFROM excellent_empWHERE year = 2020EXCEPTSELECT emp_id, emp_nameFROM excellent_empWHERE year = 2019;

查询返回的结果如下。

emp_id|emp_name------|--------    16|周仓

查询结果显示，只有“周仓”是2020年新晋的优秀员工。

对于Oracle数据库，我们需要使用MINUS运算符替换查询中的EXCEPT运算符。

对于MySQL，我们可以使用以下连接查询实现相同的结果：

SELECT t1.emp_id, t1.emp_nameFROM excellent_emp t1LEFT JOIN excellent_emp t2 ON (t1.emp_id = t2.emp_id AND t2.year= 2019)WHERE t1.year = 2020AND t2.emp_id IS NULL;

以上语句也可以用于其他4种数据库。注意WHERE和ON子句中的查询条件。

以上案例来自图书《SQL编程思想》。

ITPub博客

SQL 案例分享：优秀员工

最新文章