题解 | 统计所有课程参加培训人次

WITH T AS (SELECT staff_id,

CASE WHEN course IS NULL THEN 0
-- REPLACE(course, " ", "")去掉空格，也可以用TRIM,第二个replace是去掉逗号
-- course1,course2 --> course1course2 
-- 两个长度相减就是逗号的个数，逗号的个数+1 就是course的个数
ELSE LENGTH(REPLACE(course, " ", "")) - # 去掉空格的长度减去
LENGTH(REPLACE (REPLACE(course," ",""), ",","") ) # 
+ 1
END AS num_courses

FROM cultivate_tb)

SELECT
sum(num_courses)
AS staff_nums
FROM T