27.字符串分拆并统计的处理案例

--示例数据
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