回忆是一座桥
却是通往寂寞的牢

SQL案例 - 查询培训指定课程的员工信息

描述

某公司员工信息数据及员工培训信息数据如下:

员工信息表staff_tb(staff_id-员工id,staff_name-员工姓名,staff_gender-员工性别,post-员工岗位类别,department-员工所在部门),如下所示:

staff_id staff_name staff_gender post department
1 Angus male Financial dep1
2 Cathy female Director dep1
3 Aldis female Director dep2
4 Lawson male Engineer dep1
5 Carl male Engineer dep2
6 Ben male Engineer dep1
7 Rose female Financial dep2

员工培训信息表cultivate_tb(info_id-信息id,staff_id-员工id,course-培训课程),如下所示:

注:该公司共开设了三门课程,员工可自愿原则性培训0-3项;

info_id staff_id course
101 1 course1, course2
102 2 course2
103 3 course1, course3
104 4 course1, course2, course3
105 5 course3
106 6 NULL
107 7 course1, course2

问题:请查询培训课程course3的员工信息?

注:只要培训的课程中包含course3课程就计入结果

要求输出:员工id、姓名,按照员工id升序排序;
示例数据结果如下:

staff_id staff_name
3 Aldis
4 Lawson
5 Carl

解释:有员工3、4、5培训了course3课程,故结果如上

示例1

输入:

drop table if exists  `staff_tb` ; 
CREATE TABLE `staff_tb` (
`staff_id` int(11) NOT NULL,
`staff_name` varchar(16) NOT NULL,
`staff_gender` char(8) NOT NULL,
`post` varchar(11) NOT NULL,
`department` varchar(16) NOT NULL,
PRIMARY KEY (`staff_id`));
INSERT INTO staff_tb VALUES(1,'Angus','male','Financial','dep1'); 
INSERT INTO staff_tb VALUES(2,'Cathy','female','Director','dep1'); 
INSERT INTO staff_tb VALUES(3,'Aldis','female','Director','dep2'); 
INSERT INTO staff_tb VALUES(4,'Lawson','male','Engineer','dep1'); 
INSERT INTO staff_tb VALUES(5,'Carl','male','Engineer','dep2'); 
INSERT INTO staff_tb VALUES(6,'Ben','male','Engineer','dep1'); 
INSERT INTO staff_tb VALUES(7,'Rose','female','Financial','dep2'); 

drop table if exists  `cultivate_tb` ;   
CREATE TABLE `cultivate_tb` (
`info_id` int(11) NOT NULL,
`staff_id` int(11) NOT NULL,
`course` varchar(32) NULL,
PRIMARY KEY (`info_id`));
INSERT INTO cultivate_tb VALUES(101,1,'course1,course2');
INSERT INTO cultivate_tb VALUES(102,2,'course2');
INSERT INTO cultivate_tb VALUES(103,3,'course1,course3');
INSERT INTO cultivate_tb VALUES(104,4,'course1,course2,course3');
INSERT INTO cultivate_tb VALUES(105,5,'course3');
INSERT INTO cultivate_tb VALUES(106,6,NULL);
INSERT INTO cultivate_tb VALUES(107,7,'course1,course2');

输出:

staff_id|staff_name
3|Aldis
4|Lawson
5|Carl

答案

解法1:

select distinct
    s.staff_id,
    s.staff_name
from
    staff_tb s
    join cultivate_tb c on s.staff_id = c.staff_id
where
    c.course like '%course3%';

*本案例来自牛客网,但答案为原创,如有雷同纯属巧合*

未经允许不得转载:夕枫 » SQL案例 - 查询培训指定课程的员工信息
订阅评论
提醒
guest
0 评论
内联反馈
查看所有评论