--示例数据

CREATE TABLE tb(ID int,col varchar(50),num int)

INSERT tb SELECT 1,'aa,bb,cc',10

UNION ALL SELECT 2,'aa,aa,bb',20

UNION ALL SELECT 3,'aa,aa,bb',20

UNION ALL SELECT 4,'dd,ccc,c',30

UNION ALL SELECT 5,'ddaa,ccc',40

UNION ALL SELECT 6,'eee,ee,c',50

GO

--1. 记录数统计示例

--分拆处理需要的辅助表(由于是直接处理,所以根据col1列中最大的数据长度来创建)

DECLARE @len int

SELECT TOP 1 @len=LEN(col)+1 FROM tb ORDER BY LEN(col) DESC

IF ISNULL(@len,1)=1 RETURN

SET ROWCOUNT @len

SELECT ID=IDENTITY(int,1,1) INTO # FROM syscolumns a,syscolumns b

ALTER TABLE # ADD PRIMARY KEY(ID)

SET ROWCOUNT 0

--统计处理

SELECT data=SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID),

    [COUNT]=COUNT(DISTINCT a.ID),

    Numbers=COUNT(*)

FROM tb a,# b

WHERE b.ID<=LEN(a.col)

    AND SUBSTRING(','+a.col,b.ID,1)=','

GROUP BY SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID)

DROP TABLE #

GO

/*================================================================*/

--2. 分拆求和统计示例

--分拆处理需要的辅助表(由于是直接处理,所以根据col1列中最大的数据长度来创建)

DECLARE @len int

SELECT TOP 1 @len=LEN(col)+1 FROM tb ORDER BY LEN(col) DESC

IF ISNULL(@len,1)=1 RETURN

SET ROWCOUNT @len

SELECT ID=IDENTITY(int,1,1) INTO # FROM syscolumns a,syscolumns b

ALTER TABLE # ADD PRIMARY KEY(ID)

SET ROWCOUNT 0

--统计处理

SELECT data,SUM_num=SUM(num)

FROM(

    SELECT DISTINCT

        data=SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID),

        a.num,a.ID

    FROM tb a,# b

    WHERE b.ID<=LEN(a.col)

        AND SUBSTRING(','+a.col,b.ID,1)=','

)a GROUP BY data

DROP TABLE #

GO

/*================================================================*/

--3. 分拆求平均统计示例

--分拆处理需要的辅助表(由于是直接处理,所以根据col1列中最大的数据长度来创建)

DECLARE @len int

SELECT TOP 1 @len=LEN(col)+1 FROM tb ORDER BY LEN(col) DESC

IF ISNULL(@len,1)=1 RETURN

SET ROWCOUNT @len

SELECT ID=IDENTITY(int,1,1) INTO # FROM syscolumns a,syscolumns b

ALTER TABLE # ADD PRIMARY KEY(ID)

SET ROWCOUNT 0

--统计处理

SELECT data,

    AVG_num=CAST(AVG(CASE

        WHEN gid=1 THEN num-CAST(num as float)/(cnt+1)*cnt

        ELSE CAST(num as float)/(cnt+1) END) as decimal(10,2))

FROM(

    SELECT a.num,gid=b.ID,

        data=SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID),

        cnt=LEN(a.col)-LEN(REPLACE(a.col,',',''))

    FROM tb a,# b

    WHERE b.ID<=LEN(a.col)

        AND SUBSTRING(','+a.col,b.ID,1)=','

)a GROUP BY data

DROP TABLE #

GO