描述
某公司员工信息数据及员工培训信息数据如下:
员工信息表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%';
*本案例来自牛客网,但答案为原创,如有雷同纯属巧合*