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

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

图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

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

我这个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]

关键代码有以下几句

 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