SQLSERVER用 无中生有 的 思想 来 替代 游标 昨天在MSDN论坛看到一个帖子，帖子中LZ需要根据某列的值把其他列的值插入到额外列 帖子地址： http://social.technet.microsoft测试数据/Forums/zh-CN/3eac78ca-d071-4c00-afa0-ef48c8501745/sql-statementcolumn-nam
SQLSERVER用 无中生有 的 思想 来 替代 游标
昨天在MSDN论坛看到一个帖子，帖子中LZ需要根据某列的值把其他列的值插入到额外列
帖子地址： http://social.technet.microsoft测试数据/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 GOView 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 #tView 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，并把原表的数据全部放入临时表
获取临时表的行数，用于循环
将获取到的值都插入到#ttt这个临时表里面，然后根据ID的值做两表连接就可以得到LZ的结果
我说的 无中生有 就是[在原表里增加一个自增id方便循环，既简单又容易理解o(∩_∩)o ]
判断
我这里还用了一句
1 IF ' pay_lv_ ' + CAST ( @pay_level AS VARCHAR ( 200 )) IN ( SELECT NAME FROM SYS. [ syscolumns ] )
总结
其实如果觉得某样东西很难去实现，能不能用一个变通的方法呢？多动脑筋，办法会有的
如有不对的地方，欢迎大家拍砖o(∩_∩)o
查看更多关于SQLSERVER用无中生有的思想来替代游标的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did98003