你的位置:首页 > 数据库

[数据库]分割字符串的应用


分割字符的应用还是有不少地方的。刚好前两天在ITPUB的论坛上看有人提问这样的问题,把问题复制过来,如下:

有以下数据:

姓名      答案             开始题号          得分
张一     D;C;A;B          11               5;0;3;1
张二     F;A;C              13               6;0;2
张三     G;H;K;D;G;B    6                8;4;1;2;3;5

需求:根据姓名,在答案中根据分号相隔把答案 与得分对应分行,同一行根据开始题号逐号递增。

要得到结果:
姓名     答案     题号       得分
张一     D        11        5
张一     C        12        0
张一     A        13        3
张一     B        14        1
张二     F        13        6
张二     A        14        0
张二     C        15        2
张三     G        6         8
张三     H        7         4
张三     K        8         1
张三     D        9         2
张三     G        10        3
张三     B        11        5

 

先不管数据源为什么会这样,现在只是想方法解决问题。如果要分割数据源中的“答案”和“得分”这两个字段的字符串,可参考我之前发的文章(地址:http://www.cnblogs.com/kenyu/p/4570476.html),修改一下分割字符串函数即可满足分割的要求。但是这里是多记录的表,直接在SELECT语句上使用表值函数是不允许的。暂时想到有两个方法,第一是使用游标,第二是使用循环。

 

首先是修改后的分割函数,代码如下:

 1 IF OBJECT_ID(N'fn_split_with_rowno_set') IS NOT NULL 2 BEGIN 3   DROP FUNCTION fn_split_with_rowno_set 4 END 5 GO 6  7 CREATE FUNCTION fn_split_with_rowno_set 8 ( 9   @str    NVARCHAR(MAX)10  ,@split   NVARCHAR(20) = ','11  ,@row_no_start INT = 012 )13 RETURNS @t TABLE(row_no INT ,col NVARCHAR(500))14 AS15 BEGIN16   SET @row_no_start = ISNULL(@row_no_start,0) 17   WHILE (CHARINDEX(@split ,@str) <> 0)18   BEGIN19     INSERT @t (row_no,col)20     VALUES(@row_no_start,SUBSTRING(@str ,1 ,CHARINDEX(@split ,@str) -1))   21     SET @str = STUFF(@str ,1 ,CHARINDEX(@split ,@str) + LEN(@split) -1 ,'')22     SET @row_no_start = @row_no_start + 1 23   END 24   IF (@str <> '')25     INSERT @t (row_no,col)26     VALUES(@row_no_start ,@str)27   28   RETURN29 END30 GO

调用函数,测试下效果:

1 SELECT * FROM fn_split_with_rowno_set(N'123,abc,456,AAA,DDD,博客园',',',11)

结果如下:

 

然后是插入一些测试数据。

 1 IF OBJECT_ID(N'Tempdb..##STU_RESULT') IS NOT NULL 2 BEGIN 3   DROP TABLE ##STU_RESULT 4 END 5 GO 6  7 CREATE TABLE ##STU_RESULT ( 8   name NVARCHAR(200), 9   result NVARCHAR(MAX),10   no_start INT,11   score NVARCHAR(MAX)12 )13 GO14 15 INSERT INTO ##STU_RESULT (name, result, no_start, score)16 SELECT N'张一','D;C;A;B',11,'5;0;3;1'17 UNION ALL 18 SELECT N'张二','F;A;C',13,'6;0;2'19 UNION ALL 20 SELECT N'张三','G;H;K;D;G;B',6,'8;4;1;2;3;5'21 GO22 23 SELECT * FROM ##STU_RESULT24 25 GO

 

运行结果:

 

方法1,使用游标,把每一行(每个人)的一个或多个答案和得分分割,插入到一张临时表中,或根据需要,直接更新到业务表中。

 1 --使用游标 2 IF OBJECT_ID(N'Tempdb..#T1') IS NOT NULL 3 BEGIN 4   DROP TABLE #T1 5 END 6 GO 7  8 CREATE TABLE #T1 ( 9   name NVARCHAR(200),10   result NVARCHAR(MAX),11   no_start INT,12   score NVARCHAR(MAX)13 )14 GO15 16 DECLARE @r NVARCHAR(MAX)17 DECLARE @s NVARCHAR(MAX)18 DECLARE @name NVARCHAR(200)19 DECLARE @no_start INT20 21 DECLARE cur CURSOR LOCAL FAST_FORWARD FOR 22 SELECT name FROM ##STU_RESULT 23 GROUP BY name 24 25 OPEN cur26 FETCH NEXT FROM cur INTO @name27 28 WHILE @@FETCH_STATUS = 029 BEGIN30   SELECT @r = result,@no_start = no_start,@s = score FROM ##STU_RESULT WHERE NAME = @name31   INSERT INTO #T1 (name,result,no_start,score)32   SELECT @name,a.col,a.row_no,b.col FROM fn_split_with_rowno_set(@r,';',@no_start) AS a 33   LEFT JOIN fn_split_with_rowno_set(@s,';',@no_start) AS b ON a.row_no = b.row_no34 FETCH NEXT FROM cur INTO @name35 END36 37 CLOSE cur38 DEALLOCATE cur39 40 SELECT * FROM #T141 GO

 

运行结果:

 

方法2,使用循环。

 1 --使用循环。 2 IF OBJECT_ID(N'Tempdb..#T3') IS NOT NULL 3 BEGIN 4   DROP TABLE #T3 5 END 6 GO 7  8 CREATE TABLE #T3 ( 9   name NVARCHAR(200),10   result NVARCHAR(MAX),11   no_start INT,12   score NVARCHAR(MAX)13 )14 GO15 16 IF OBJECT_ID(N'Tempdb..#T2') IS NOT NULL17 BEGIN18   DROP TABLE #T219 END20 GO21 22 CREATE TABLE #T2 (23   id_ INT,24   name NVARCHAR(200),25   result NVARCHAR(MAX),26   no_start INT,27   score NVARCHAR(MAX)28 )29 GO30 31 INSERT INTO #T2 (id_,name,result,no_start,score)32 SELECT ROW_NUMBER() OVER (ORDER BY name ASC),name,result,no_start,score FROM ##STU_RESULT33 --SELECT * FROM #T234 35 36 DECLARE @r NVARCHAR(MAX)37 DECLARE @s NVARCHAR(MAX)38 DECLARE @name NVARCHAR(200)39 DECLARE @no_start INT40 DECLARE @i INT41 DECLARE @j INT42 43 SET @i = 144 SELECT @j = MAX(id_) FROM #T245 46 BEGIN TRAN 47 WHILE @i <= @j 48 BEGIN49   SELECT @name=name,@r=result,@no_start=no_start,@s=score FROM #T2 WHERE id_=@i50   51   INSERT INTO #T3 (name,result,no_start,score)52   SELECT @name,a.col,a.row_no,b.col FROM fn_split_with_rowno_set(@r,';',@no_start) AS a 53   LEFT JOIN fn_split_with_rowno_set(@s,';',@no_start) AS b ON a.row_no=b.row_no54   55   SET @i = @i + 156 END57 COMMIT TRAN 58 59 SELECT * FROM #T360 GO

 

运行结果:

 

这两个方法有什么区别?结果看起来没什么区别,但是在我的笔记本上测试,源表插入10000条记录,运行速度,循环比游标快了几倍。至于张二为什么在张三后面,这可能是关乎于我的数据库的Collation使用的是SQL_Latin1_General_CP1_CI_AS有关了。这个问题待我有空的时候研究下。