SQLSERVER用无中生有的思想来替代游标
|
昨天在MSDN论坛看到一个帖子，帖子中LZ需要根据某列的值把其他列的值插入到额外列 帖子地址：http://social.technet.microsoft.com/Forums/zh-CN/3eac78ca-d071-4c00-afa0-ef48c8501745/sql-statementcolumn-namecolumnsql- 建表脚本：
图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 SELECT client,[pay_level],( CASE pay_level 2 WHEN 1 THEN pay_lv_1 3 2 THEN pay_lv_2 4 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] FROM #tt] 9 10 DROP TABLE ]
图5 上面两个方法：CASE WHEN和UNPIVOT函数可以用拼接SQL的方法来做，不过由于本人功力不够，写不出来 （3）游标 ?我不喜欢使用游标，主要有两个原因 1、每次用的时候，要打开笔记本看语法 2、占用资源 ?我使用了下面的sql语句来解决LZ的问题
IF object_id(#ttt') IS NOT NULL TABLE #ttt 3 #temptb 4 TABLE #temptb 5 DECLARE @i INT 7 --用于循环的 SET @i = 1 9 @pay_level 10 保存pay_level字段的值 11 @COUNT 12 保存#t1表的总行数值 13 @pay_lv 14 用于保存pay_lv的值 15 @sql NVARCHAR(2000) 16 17 CREATE TABLE #ttt (ID INT IDENTITY(1,0)">1),pay_cost INT ) 18 19 SELECT IDENTITY( INT,0)">1 ) AS ID,255)">INTO #temptb FROM t1 20 21 22 获取#t1表的总行数 23 SELECT @COUNT = COUNT(*) FROM 24 WHILE <= @COUNT 25 BEGIN 26 @pay_level WHERE id = @i 27 判断列名是否存在，不存在就插入0 28 pay_lv_' + CAST(VARCHAR(200)) IN ( SELECT NAME FROM SYS.syscolumns] ) 29 30 用拼接sql的方法来获得pay_lv列对应的值，然后插入到#ttt表 31 @sql = Nselect @pay_lv=pay_lv_ from #temptb where id=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 37 VALUES(0) 38 39 + 40 41 42 43 44 SELECT A.45 AS A 46 INNER JOIN AS B ON A.ID] = B.47 ORDER BY A.ASC 48 49 50 ] 不过这篇文章的重点不是拼接SQL 重点是怎麽模仿游标 其实这个方法是最原始的方法，之前解决论坛问题的时候用过，想不到这次也能用上
关键代码有以下几句 2 4 5 7 -------------------------------- 12 14 ASC ? 原表是没有自增id的，我建一个临时表#temptb，临时表有一个自增id，并把原表的数据全部放入临时表 获取临时表的行数，用于循环 每次执行的时候根据?WHERE?? id = @i 来逐行逐行获取值，变量@i每次循环都递增1 将获取到的值都插入到#ttt这个临时表里面，然后根据ID的值做两表连接就可以得到LZ的结果 我说的无中生有就是“在原表里增加一个自增id方便循环，既简单又容易理解o(∩_∩)o ” ? 判断 我这里还用了一句 ] )用于判断要获取值的pay_lv_列是否存在，如果存在就插入pay_lv_列的值，如果不存在就插入0 总结 其实如果觉得某样东西很难去实现，能不能用一个变通的方法呢？多动脑筋，办法会有的 如有不对的地方，欢迎大家拍砖o(∩_∩)o （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
