欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-06-08 11:11:08
...

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-


建表脚本

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

 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

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

图1

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

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

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

图2

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

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

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

图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列

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

 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;

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

图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]

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

图5

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

(3)游标

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

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

2、占用资源

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

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

 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


重点是怎麽模仿游标

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

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

  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