SQL 拆分字符串

Question

SQL拆分字符串

Answer 1

SELECT SUBSTRING(T1.Teacher,T2.number,CHARINDEX(',',T1.Teacher+',',T2.number)-T2.number)Teacher
,COUNT(1)[Count]
FROM Employees T1
JOIN master..spt_values T2 ON T2.type='P'
WHERE CHARINDEX(',',','+T1.Teacher,T2.number)=T2.number
GROUP BY SUBSTRING(T1.Teacher,T2.number,CHARINDEX(',',T1.Teacher+',',T2.number)-T2.number)

Answer 2

SQL2005+可以用这个;WITH CTE AS(
SELECT T2.V FROM
(SELECT CAST('<V>'+REPLACE(Teacher,',','</V><V>')+'</V>' AS XML)VS FROM Employees)T1
CROSS APPLY(SELECT N.V.value('.','VARCHAR(100)')V FROM T1.VS.nodes('/V') N(V))T2
)
SELECT V AS Teacher,COUNT(1)AS[Count]
FROM CTE GROUP BY V

Answer 3

CREATE TABLE #Employees(  Class    VarChar(40)   NOT NULL,  Teacher  NVarChar(200)  NOT NULL,);Insert Into #Employees(Class, Teacher)  Values('001',N'张三,李四,王二');Insert Into #Employees(Class, Teacher)  Values('002',N'李四');Insert Into #Employees(Class, Teacher)  Values('003',N'李四,王二');Insert Into #Employees(Class, Teacher)  Values('004',N'张三,王二');Insert Into #Employees(Class, Teacher)  Values('005',N'张三');Insert Into #Employees(Class, Teacher)  Values('006',N'张三,李四,王二');Insert Into #Employees(Class, Teacher)  Values('007',N'李四,王二');Insert Into #Employees(Class, Teacher)  Values('008',N'张三,李四,王二');Insert Into #Employees(Class, Teacher)  Values('009',N'张三,王二'); 
with cte as
(
select *,
COL=convert(xml,'<v><v>'+replace(replace(rtrim(ltrim(Teacher)),',','</v><v>'),'<v></v>','')+'</v></v>')   from #Employees a
)
,cte1 as
(
select * from cte A
outer apply (select col1=T.C.value('.','nvarchar(100)') from A.COL.nodes('/v/v')T(C)) B
)
select col1,COUNT(*) 
from cte1 group by col1