SQL 拆分字符串

【问题】

CREATE TABLE dbo.Employees
(
Class VarChar(40) NOT NULL,
Teacher VarChar(200) NOT NULL,
);

Insert Into dbo.Employees(Class, Teacher) Values(‘001’,‘张三, 李四, 王二’);
Insert Into dbo.Employees(Class, Teacher) Values(‘002’,‘李四’);
Insert Into dbo.Employees(Class, Teacher) Values(‘003’,‘李四, 王二’);
Insert Into dbo.Employees(Class, Teacher) Values(‘004’,‘张三, 王二’);
Insert Into dbo.Employees(Class, Teacher) Values(‘005’,‘张三’);
Insert Into dbo.Employees(Class, Teacher) Values(‘006’,‘张三, 李四, 王二’);
Insert Into dbo.Employees(Class, Teacher) Values(‘007’,‘李四, 王二’);
Insert Into dbo.Employees(Class, Teacher) Values(‘008’,‘张三, 李四, 王二’);
Insert Into dbo.Employees(Class, Teacher) Values(‘009’,‘张三, 王二’);

期望获得结果：
Teacher  Count  
张三    6
李四    6
王二    7

【回答】
SQL 拆串的处理实现很麻烦，很难按自然思维（把 Teacher 拆分成多条，再合并后分组汇总）写出代码。因此这种情况适合用 SPL 来做，脚本直观易懂：