• 分割字符串的应用


    分割字符的应用还是有不少地方的。刚好前两天在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

    先不管数据源为什么会这样,现在只是想方法解决问题。如果要分割数据源中的“答案”和“得分”这两个字段的字符串,可参考我之前发的文章(几种分割字符串实现方法的比较),修改一下分割字符串函数即可满足分割的要求。但是这里是多记录的表,直接在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 = 0
    12 )
    13 RETURNS @t TABLE(row_no INT ,col NVARCHAR(500))
    14 AS
    15 BEGIN
    16     SET @row_no_start = ISNULL(@row_no_start,0) 
    17     WHILE (CHARINDEX(@split ,@str) <> 0)
    18     BEGIN
    19         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     RETURN
    29 END
    30 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 GO
    14 
    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 GO
    22 
    23 SELECT * FROM ##STU_RESULT
    24 
    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 GO
    15 
    16 DECLARE @r NVARCHAR(MAX)
    17 DECLARE @s NVARCHAR(MAX)
    18 DECLARE @name NVARCHAR(200)
    19 DECLARE @no_start INT
    20 
    21 DECLARE cur CURSOR LOCAL FAST_FORWARD FOR 
    22 SELECT name FROM ##STU_RESULT 
    23 GROUP BY name 
    24 
    25 OPEN cur
    26 FETCH NEXT FROM cur INTO @name
    27 
    28 WHILE @@FETCH_STATUS = 0
    29 BEGIN
    30     SELECT @r = result,@no_start = no_start,@s = score  FROM ##STU_RESULT WHERE NAME = @name
    31     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_no
    34 FETCH NEXT FROM cur INTO @name
    35 END
    36 
    37 CLOSE cur
    38 DEALLOCATE cur
    39 
    40 SELECT * FROM #T1
    41 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 GO
    15 
    16 IF OBJECT_ID(N'Tempdb..#T2') IS NOT NULL
    17 BEGIN
    18     DROP TABLE #T2
    19 END
    20 GO
    21 
    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 GO
    30 
    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_RESULT
    33 --SELECT * FROM #T2
    34 
    35 
    36 DECLARE @r NVARCHAR(MAX)
    37 DECLARE @s NVARCHAR(MAX)
    38 DECLARE @name NVARCHAR(200)
    39 DECLARE @no_start INT
    40 DECLARE @i INT
    41 DECLARE @j INT
    42 
    43 SET @i = 1
    44 SELECT @j = MAX(id_) FROM #T2
    45 
    46 BEGIN TRAN 
    47 WHILE @i <= @j 
    48 BEGIN
    49     SELECT @name=name,@r=result,@no_start=no_start,@s=score FROM #T2 WHERE id_=@i
    50     
    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_no
    54     
    55     SET @i = @i + 1
    56 END
    57 COMMIT TRAN 
    58 
    59 SELECT * FROM #T3
    60 GO

    运行结果:

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

  • 相关阅读:
    bzoj3653: 谈笑风生
    bzoj1858: [Scoi2010]序列操作
    bzoj1857: [Scoi2010]传送带
    bzoj1856: [Scoi2010]字符串
    bzoj1855: [Scoi2010]股票交易
    bzoj1854: [Scoi2010]游戏
    bzoj1853: [Scoi2010]幸运数字
    斜堆,非旋转treap,替罪羊树
    NOI2003 文本编辑器
    A_star poj2449 k短路
  • 原文地址:https://www.cnblogs.com/fishparadise/p/4570598.html
Copyright © 2020-2023  润新知