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

逃离我推掉我的手 2024-02-19 17:48 38阅读 0赞

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