sql 数据库怎么 统计 出差人员的次数

我要的效果是:
姓名 出差次数
向晟 1
向明 1
龙恺 2
.............

求解答！！！！！！
就是出差人数以逗号隔开在分组

select ITEM_姓名, count(*) as 次数 from 表 group by ITEM_姓名

CREATE FUNCTION [dbo].[FN_SPLIT](@Long_str varchar(max),@split_str varchar(100))    
RETURNS  @tmp TABLE(        
    ID      inT IDENTITY PRIMARY KEY,      
    SPLIT   varchar(max)    
)    
AS   
BEGIN 
        DECLARE @sxml XML

        SET @sxml='<root><node>'+REPLACE(@Long_str,@split_str,'</node><node>')+'</node></root>'
        INSERT INTO @tmp([SPLIT])
        SELECT b.value('.','varchar(max)') FROM @sxml.nodes('root/node') AS s(b)


    RETURN     
END 
GO
 WITH a(item_name,item_person) AS (
    SELECT 'A','B,C,D'  UNION
    SELECT 'E' ,'C'  UNION
    SELECT 'B' ,'B,F' 
)
SELECT fs.[SPLIT],COUNT(0) FROM (
SELECT CASE WHEN CHARINDEX(','+a.item_name+',',','+a.item_person+',')>0 THEN a.item_person ELSE a.item_name+','+a.item_person  END
AS Persons
FROM a ) t CROSS APPLY dbo.FN_SPLIT(t.Persons,',') AS fs
GROUP BY fs.[SPLIT]

CREATE FUNCTION [dbo].[FN_SPLIT](@Long_str varchar(max),@split_str varchar(100))    
RETURNS  @tmp TABLE(        
    ID      inT IDENTITY PRIMARY KEY,      
    SPLIT   varchar(max)    
)    
AS   
BEGIN 
        DECLARE @sxml XML

        SET @sxml='<root><node>'+REPLACE(@Long_str,@split_str,'</node><node>')+'</node></root>'
        INSERT INTO @tmp([SPLIT])
        SELECT b.value('.','varchar(max)') FROM @sxml.nodes('root/node') AS s(b)


    RETURN     
END 
GO
 WITH a(item_name,item_person) AS (
    SELECT 'A','B,C,D'  UNION
    SELECT 'E' ,'C'  UNION
    SELECT 'B' ,'B,F' 
)
SELECT fs.[SPLIT],COUNT(0) FROM (
SELECT CASE WHEN CHARINDEX(','+a.item_name+',',','+a.item_person+',')>0 THEN a.item_person ELSE a.item_name+','+a.item_person  END
AS Persons
FROM a ) t CROSS APPLY dbo.FN_SPLIT(t.Persons,',') AS fs
GROUP BY fs.[SPLIT]

网站程序写的不是很好，老发重复。我写的思路，先将Item_name和Item_出差人员合并（在一行记录一个人不能算两次）
然后用逗号分隔合并出的人员名单再统计数量

如果你不想建一个function，可以把function中的逻辑直接写到SQL 语句里

我还是给你写出来吧，不需要新建function的版本：

 WITH a(item_name,item_person) AS (
    SELECT 'A','B,C,D'  UNION
    SELECT 'E' ,'C'  UNION
    SELECT 'B' ,'B,F' 
)
SELECT z.n,COUNT(0) FROM (
   SELECT CONVERT(XML,'<r><n>'+replace(CASE WHEN CHARINDEX(','+a.item_name+',',','+a.item_person+',')>0 THEN a.item_person ELSE a.item_name+','+a.item_person  END
,',','</n><n>')+'</n></r>') AS Persons
   FROM a ) t 
CROSS APPLY (SELECT b.value('.','varchar(max)') AS n FROM t.Persons.nodes('r/n') AS s(b)) z
GROUP BY z.n
-------------     对应你的表 ------------------
SELECT z.n,COUNT(0) FROM (
   SELECT CONVERT(XML,'<r><n>'+replace(CASE WHEN CHARINDEX(','+a.item_姓名+',',','+a.item_出差人员+',')>0 THEN a.item_出差人员 ELSE a.a.item_姓名+','+a.item_出差人员  END
,',','</n><n>')+'</n></r>') AS Persons
   FROM 你的表 as a ) t 
CROSS APPLY (SELECT b.value('.','varchar(max)') AS n FROM t.Persons.nodes('r/n') AS s(b)) z
GROUP BY z.n