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

SQL案例 - 统计所有课程参加培训人次

描述

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

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

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

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

问题:请统计该公司所有课程参加培训人次?

示例数据结果如下:

staff_nums
11

解释:course1课程共有员工1、3、4、7共4名员工培训;

course2课程共有员工1、2、4、7共4名员工培训;

course3课程共有员工3、4、5共3名员工培训。

示例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_nums
11

答案

解法1:

select
    sum(t.course_nbr) staff_nums
from
    (
        select
            *,
            length (course) - length (replace (course, ',', '')) + 1 course_nbr
        from
            cultivate_tb
    ) t;

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

未经允许不得转载:夕枫 » SQL案例 - 统计所有课程参加培训人次
订阅评论
提醒
guest
0 评论
内联反馈
查看所有评论