当前位置:Gxlcms > mysql > SQLSERVER用无中生有的思想来替代游标

SQLSERVER用无中生有的思想来替代游标

时间:2021-07-01 10:21:17 帮助过:21人阅读

SQLSERVER用 无中生有 的 思想 来 替代 游标 昨天在MSDN论坛看到一个帖子,帖子中LZ需要根据某列的值把其他列的值插入到额外列 帖子地址: http://social.technet.microsoft.com/Forums/zh-CN/3eac78ca-d071-4c00-afa0-ef48c8501745/sql-statementcolumn-nam

SQLSERVER用无中生有思想替代游标

昨天在MSDN论坛看到一个帖子,帖子中LZ需要根据某列的值把其他列的值插入到额外列

帖子地址:http://social.technet.microsoft.com/Forums/zh-CN/3eac78ca-d071-4c00-afa0-ef48c8501745/sql-statementcolumn-namecolumnsql-


建表脚本

 1 USE tempdb
 2 GO
 3 
 4 --建表
 5 CREATE TABLE t1
 6 (
 7   client VARCHAR(10) ,
 8   pay_level INT ,
 9   pay_lv_1 INT ,
10   pay_lv_2 INT ,
11   pay_lv_3 INT ,
12   pay_lv_4 INT ,
13   pay_lv_5 INT ,
14   pay_lv_6 INT ,
15   pay_lv_7 INT ,
16   pay_lv_8 INT ,
17   pay_lv_9 INT ,
18   pay_lv_10 INT ,
19   pay_lv_11 INT ,
20   pay_lv_12 INT ,
21   pay_lv_13 INT ,
22   pay_lv_14 INT ,
23   pay_lv_15 INT ,
24   pay_lv_16 INT ,
25   pay_lv_17 INT ,
26   pay_lv_18 INT ,
27   pay_lv_19 INT ,
28   pay_lv_20 INT ,
29   pay_lv_21 INT ,
30   pay_lv_22 INT ,
31   pay_lv_23 INT ,
32   pay_lv_24 INT ,
33   pay_lv_25 INT,
34 );
35 
36 
37 --插入测试数据
38 DECLARE @i INT
39 SET @i = 1
40 WHILE @i < 8 
41     BEGIN
42         INSERT  INTO t1 ( client, pay_level, pay_lv_1, pay_lv_2, pay_lv_3,
43                            pay_lv_4, pay_lv_5, pay_lv_6, pay_lv_7, pay_lv_8,
44                            pay_lv_9, pay_lv_10, pay_lv_11, pay_lv_12,
45                            pay_lv_13, pay_lv_14, pay_lv_15, pay_lv_16,
46                            pay_lv_17, pay_lv_18, pay_lv_19, pay_lv_20,
47                            pay_lv_21, pay_lv_22, pay_lv_23, pay_lv_24,
48                            pay_lv_25 )
49                 SELECT  'client' + CAST(@i AS VARCHAR(10)),
50                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
51                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
52                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
53                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
54                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
55                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
56                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
57                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
58                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
59                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
60                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
61                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
62                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND()
63           SET @i=@i+1
64 
65     END
66 
67 SELECT * FROM t1
68 GO

View Code

图1

LZ说原表就是类似上面那样,实际表中pay_lv_会有很多列至少100列,我这里为了测试只建了25个pay_lv_列

而LZ希望select出来的结果是下图那样

图2

client列和pay_level列不变,增加一个pay_cost列

pay_cost列根据pay_level列的值去取pay_lv_列的值,或者我用下面的图片会更加明白

图3

例如第6行,pay_level的值是6,那么就去pay_lv_6这一列的值(值是20)把他放到pay_cost列里

其他也是一样,第二行pay_level的值是10,那就去pay_lv_10这一列的值(值是17)把他放到pay_cost列里

如此类推


要select出图2的结果,有下面几种方法

1、case when

2、UNPIVOT函数

3、游标

我这里再建另外一个表,这个表跟原表是一样的,只是数据没有那么多,pay_lv_列数只有3列

 1 USE tempdb
 2 GO
 3 
 4 
 5 CREATE TABLE #t
 6 (
 7   client VARCHAR(10) ,
 8   pay_level INT ,
 9   pay_lv_1 INT ,
10   pay_lv_2 INT ,
11   pay_lv_3 INT
12 );
13 
14 INSERT INTO #t ( client ,
15           pay_level ,
16           pay_lv_1 ,
17           pay_lv_2 ,
18           pay_lv_3
19         )
20 VALUES  ( 'client1' , -- client - varchar(10)
21           1, -- pay_level - int
22           10 , -- pay_lv_1 - int
23           12 , -- pay_lv_2 - int
24           14  -- pay_lv_3 - int
25         )
26 
27 
28 INSERT INTO #t ( client ,
29           pay_level ,
30           pay_lv_1 ,
31           pay_lv_2 ,
32           pay_lv_3
33         )
34 VALUES  ( 'client2' , -- client - varchar(10)
35           3, -- pay_level - int
36           21 , -- pay_lv_1 - int
37           22 , -- pay_lv_2 - int
38           23  -- pay_lv_3 - int
39         )
40 
41 INSERT INTO #t ( client ,
42           pay_level ,
43           pay_lv_1 ,
44           pay_lv_2 ,
45           pay_lv_3
46         )
47 VALUES  ( 'client3' , -- client - varchar(10)
48           2, -- pay_level - int
49           30 , -- pay_lv_1 - int
50           32 , -- pay_lv_2 - int
51           33  -- pay_lv_3 - int
52         )
53 
54 SELECT * FROM #t

View Code

(1)case when

1 SELECT  client,[pay_level],( CASE pay_level
2                   WHEN 1 THEN pay_lv_1
3                   WHEN 2 THEN pay_lv_2
4                   WHEN 3 THEN pay_lv_3
5                   ELSE 0
6                 END) AS 'pay_cost'
7 FROM    #t;

图4

(2)UNPIVOT函数

 1 SELECT  * INTO #tt
 2 FROM    ( SELECT    *
 3           FROM      #t
 4         ) p UNPIVOT
 5    ( pay_cost FOR pay_lv IN ( pay_lv_1, pay_lv_2, pay_lv_3 ) )AS unpvt
 6 WHERE   CAST(RIGHT(pay_lv, 1) AS INT) = pay_level
 7 
 8 SELECT [client],[pay_level],[pay_cost] FROM [#tt]
 9 
10 DROP TABLE [#tt]

图5

上面两个方法:CASE WHEN和UNPIVOT函数可以用拼接SQL的方法来做,不过由于本人功力不够,写不出来

(3)游标

我不喜欢使用游标,主要有两个原因

1、每次用的时候,要打开笔记本看语法

2、占用资源

我使用了下面的sql语句来解决LZ的问题

 1 IF object_id('#ttt') IS NOT NULL
 2 DROP TABLE #ttt
 3 IF object_id('#temptb') IS NOT NULL
 4 DROP TABLE #temptb
 5 
 6 DECLARE @i INT
 7   --用于循环的
 8 SET @i = 1
 9 DECLARE @pay_level INT
10   --保存pay_level字段的值
11 DECLARE @COUNT INT
12    --保存#t1表的总行数值
13 DECLARE @pay_lv INT
14   --用于保存pay_lv的值
15 DECLARE @sql NVARCHAR(2000)
16 
17 CREATE TABLE #ttt (ID INT IDENTITY(1,1), pay_cost INT )
18 
19 SELECT  IDENTITY( INT,1,1 ) AS ID, * INTO    #temptb FROM  t1
20 
21 
22 --获取#t1表的总行数
23 SELECT  @COUNT = COUNT(*) FROM    [#temptb]
24 WHILE @i <= @COUNT 
25     BEGIN
26         SELECT  @pay_level = [pay_level] FROM    [#temptb] WHERE   id = @i
27     --判断列名是否存在,不存在就插入0
28         IF 'pay_lv_' + CAST(@pay_level AS VARCHAR(200)) IN ( SELECT   NAME FROM     SYS.[syscolumns] ) 
29             BEGIN
30                 --用拼接sql的方法来获得pay_lv列对应的值,然后插入到#ttt表
31                 SET @sql = N'select ' + ' @pay_lv=pay_lv_' + CAST(@pay_level AS NVARCHAR(200)) + ' from #temptb where id=' + CAST(@i AS NVARCHAR(20))
32                 EXEC sp_executesql @sql, N'@pay_lv   int   output ', @pay_lv OUTPUT
33                 INSERT  INTO #ttt VALUES  (@pay_lv)
34             END
35         ELSE 
36             BEGIN
37                 INSERT  INTO #ttt VALUES(0)
38             END
39         SET @i = @i + 1
40     END
41 
42 
43 
44 SELECT  A.[client], A.[pay_level], B.[pay_cost]
45 FROM    [#temptb] AS A
46 INNER JOIN [#ttt] AS B ON A.[ID] = B.[ID]
47 ORDER BY A.[ID] ASC
48 
49 DROP TABLE [#temptb]
50 DROP TABLE [#ttt]

View Code


我这个sql语句也需要拼接sql来达到LZ想要的效果

不过这篇文章的重点不是拼接SQL


重点是怎麽模仿游标

其实这个方法是最原始的方法,之前解决论坛问题的时候用过,想不到这次也能用上

  1 USE tempdb
  2 GO
  3 
  4 --建表
  5 CREATE TABLE t1
  6 (
  7   client VARCHAR(10) ,
  8   pay_level INT ,
  9   pay_lv_1 INT ,
 10   pay_lv_2 INT ,
 11   pay_lv_3 INT ,
 12   pay_lv_4 INT ,
 13   pay_lv_5 INT ,
 14   pay_lv_6 INT ,
 15   pay_lv_7 INT ,
 16   pay_lv_8 INT ,
 17   pay_lv_9 INT ,
 18   pay_lv_10 INT ,
 19   pay_lv_11 INT ,
 20   pay_lv_12 INT ,
 21   pay_lv_13 INT ,
 22   pay_lv_14 INT ,
 23   pay_lv_15 INT ,
 24   pay_lv_16 INT ,
 25   pay_lv_17 INT ,
 26   pay_lv_18 INT ,
 27   pay_lv_19 INT ,
 28   pay_lv_20 INT ,
 29   pay_lv_21 INT ,
 30   pay_lv_22 INT ,
 31   pay_lv_23 INT ,
 32   pay_lv_24 INT ,
 33   pay_lv_25 INT,
 34 );
 35 
 36 
 37 --插入测试数据
 38 DECLARE @i INT
 39 SET @i = 1
 40 WHILE @i < 8 
 41     BEGIN
 42         INSERT  INTO t1 ( client, pay_level, pay_lv_1, pay_lv_2, pay_lv_3,
 43                            pay_lv_4, pay_lv_5, pay_lv_6, pay_lv_7, pay_lv_8,
 44                            pay_lv_9, pay_lv_10, pay_lv_11, pay_lv_12,
 45                            pay_lv_13, pay_lv_14, pay_lv_15, pay_lv_16,
 46                            pay_lv_17, pay_lv_18, pay_lv_19, pay_lv_20,
 47                            pay_lv_21, pay_lv_22, pay_lv_23, pay_lv_24,
 48                            pay_lv_25 )
 49                 SELECT  'client' + CAST(@i AS VARCHAR(10)),
 50                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
 51                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
 52                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
 53                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
 54                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
 55                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
 56                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
 57                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
 58                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
 59                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
 60                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
 61                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND(),
 62                         ( 20 + 1 ) * RAND(), ( 20 + 1 ) * RAND()
 63           SET @i=@i+1
 64 
 65     END
 66 
 67 SELECT * FROM t1
 68 GO
 69 
 70 --ALTER TABLE [t1] DROP COLUMN [pay_lv_2]
 71 
 72 
 73 -----------------------------------------------------
 74 IF object_id('#ttt') IS NOT NULL
 75 DROP TABLE #ttt
 76 IF object_id('#temptb') IS NOT NULL
 77 DROP TABLE #temptb
 78 
 79 DECLARE @i INT
 80   --用于循环的
 81 SET @i = 1
 82 DECLARE @pay_level INT
 83   --保存pay_level字段的值
 84 DECLARE @COUNT INT
 85    --保存t1表的总行数值
 86 DECLARE @pay_lv INT
 87   --用于保存pay_lv的值
 88 DECLARE @sql NVARCHAR(2000)
 89 
 90 CREATE TABLE #ttt (ID INT IDENTITY(1,1), pay_cost INT )
 91 
 92 SELECT  IDENTITY( INT,1,1 ) AS ID, * INTO    #temptb FROM  t1
 93 
 94 
 95 --获取t1表的总行数
 96 SELECT  @COUNT = COUNT(*) FROM    [#temptb]
 97 WHILE @i <= @COUNT 
 98     BEGIN
 99         SELECT  @pay_level = [pay_level] FROM    [#temptb] WHERE   id = @i
100     --判断列名是否存在,不存在就插入0
101         IF 'pay_lv_' + CAST(@pay_level AS VARCHAR(200)) IN ( SELECT   NAME FROM     SYS.[syscolumns] ) 
102             BEGIN
103                 --用拼接sql的方法来获得pay_lv列对应的值,然后插入到#ttt表
104                 SET @sql = N'select ' + ' @pay_lv=pay_lv_' + CAST(@pay_level AS NVARCHAR(200)) + ' from #temptb where id=' + CAST(@i AS NVARCHAR(20))
105                 EXEC sp_executesql @sql, N'@pay_lv   int   output ', @pay_lv OUTPUT
106                 INSERT  INTO #ttt VALUES  (@pay_lv)
107             END
108         ELSE 
109             BEGIN
110                 INSERT  INTO #ttt VALUES(0)
111             END
112         SET @i = @i + 1
113     END
114 
115 
116 
117 SELECT  A.[client], A.[pay_level], B.[pay_cost]
118 FROM    [#temptb] AS A
119 INNER JOIN [#ttt] AS B ON A.[ID] = B.[ID]
120 ORDER BY A.[ID] ASC
121 
122 DROP TABLE [#temptb]
123 DROP TABLE [#ttt]

View Code


关键代码有以下几句

 1 CREATE TABLE #ttt (ID INT IDENTITY(1,1), pay_cost INT )
 2 
 3 SELECT  IDENTITY( INT,1,1 ) AS ID, * INTO    #temptb FROM  t1
 4 
 5 --获取#t1表的总行数
 6 SELECT  @COUNT = COUNT(*) FROM    [#temptb]
 7 WHILE @i <= @COUNT 
 8 SELECT  @pay_level = [pay_level] FROM    [#temptb] WHERE   id = @i
 9 SET @i = @i + 1
10 ----------------------------------
11 SELECT  A.[client], A.[pay_level], B.[pay_cost]
12 FROM    [#temptb] AS A
13 INNER JOIN [#ttt] AS B ON A.[ID] = B.[ID]
14 ORDER BY A.[ID] ASC

原表是没有自增id的,我建一个临时表#temptb,临时表有一个自增id,并把原表的数据全部放入临时表

获取临时表的行数,用于循环

每次执行的时候根据 WHERE id = @i 来逐行逐行获取值,变量@i每次循环都递增1

将获取到的值都插入到#ttt这个临时表里面,然后根据ID的值做两表连接就可以得到LZ的结果

我说的无中生有就是“在原表里增加一个自增id方便循环,既简单又容易理解o(∩_∩)o ”


判断

我这里还用了一句

1 IF 'pay_lv_' + CAST(@pay_level AS VARCHAR(200)) IN ( SELECT   NAME FROM     SYS.[syscolumns] ) 

用于判断要获取值的pay_lv_列是否存在,如果存在就插入pay_lv_列的值,如果不存在就插入0


总结

其实如果觉得某样东西很难去实现,能不能用一个变通的方法呢?多动脑筋,办法会有的

如有不对的地方,欢迎大家拍砖o(∩_∩)o

人气教程排行