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

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

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

---

建表脚本：

  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列

  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]

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

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

---

重点是怎麽模仿游标

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

  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