SQL 案例分享:优秀员工

今天我们介绍一个优秀员工分析的案例,涉及的知识点是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以及SQLite
SELECT emp_id, emp_name -- 2019年优秀员工
FROM excellent_emp
WHERE year = 2019
INTERSECT
SELECT emp_id, emp_name -- 2020年优秀员工
FROM excellent_emp
WHERE year = 2020;

查询返回的结果如下。

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

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

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

SELECT t1.emp_id, t1.emp_name
FROM excellent_emp t1
JOIN excellent_emp t2
ON (t1.emp_id = t2.emp_id AND t1.year = 2019 AND t2.year = 2020);

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

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

SELECT emp_id, emp_name
FROM excellent_emp
WHERE year = 2019
UNION ALL
SELECT emp_id, emp_name
FROM excellent_emp
WHERE year = 2020;

查询返回的结果如下。

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

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

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

-- Microsoft SQL Server、PostgreSQL以及SQLite
SELECT emp_id, emp_name
FROM excellent_emp
WHERE year = 2020
EXCEPT
SELECT emp_id, emp_name
FROM excellent_emp
WHERE year = 2019;

查询返回的结果如下。

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

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

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

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

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

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

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

发表评论

登录后才能评论
网站客服
网站客服
申请收录 侵权处理
分享本页
返回顶部