澳门新葡亰网址下载SQLSERVER用无中生有的思想来替代游标

by admin on 2020年1月20日

  在翻阅本文此前希望你看一下本身在论坛里发的一张帖子,地址如下:

SQLSEHavalVEENCORE用兴妖作怪的思考来顶替游标

前不久在MSDN论坛见到多个帖子,帖子中LZ须要基于某列的值把任何列的值插入到额外列

帖子地址:


建表脚本

澳门新葡亰网址下载 1澳门新葡亰网址下载 2

 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 INT39 SET @i = 140 WHILE @i < 8 41   BEGIN42     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+164 65   END66 67 SELECT * FROM t168 GO

View Code

澳门新葡亰网址下载 3

图1

LZ说原表正是左近上面那样,实际表中pay_lv_会有点不清列最少100列,作者这里为了测量试验只建了二十多个pay_lv_列

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

 澳门新葡亰网址下载 4

图2

client列和pay_level列不改变,增添三个pay_cost列

pay_cost列根据pay_level列的值去取pay_lv_列的值,或然笔者用上面包车型地铁图形会特别清楚

 澳门新葡亰网址下载 5

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

澳门新葡亰网址下载 6澳门新葡亰网址下载 7

 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 INT12 );13 14 INSERT INTO #t ( client ,15      pay_level ,16      pay_lv_1 ,17      pay_lv_2 ,18      pay_lv_319     )20 VALUES ( 'client1' , -- client - varchar(10)21      1, -- pay_level - int22      10 , -- pay_lv_1 - int23      12 , -- pay_lv_2 - int24      14 -- pay_lv_3 - int25     )26 27 28 INSERT INTO #t ( client ,29      pay_level ,30      pay_lv_1 ,31      pay_lv_2 ,32      pay_lv_333     )34 VALUES ( 'client2' , -- client - varchar(10)35      3, -- pay_level - int36      21 , -- pay_lv_1 - int37      22 , -- pay_lv_2 - int38      23 -- pay_lv_3 - int39     )40 41 INSERT INTO #t ( client ,42      pay_level ,43      pay_lv_1 ,44      pay_lv_2 ,45      pay_lv_346     )47 VALUES ( 'client3' , -- client - varchar(10)48      2, -- pay_level - int49      30 , -- pay_lv_1 - int50      32 , -- pay_lv_2 - int51      33 -- pay_lv_3 - int52     )53 54 SELECT * FROM #t

View Code

(1)case when

1 SELECT client,[pay_level],( CASE pay_level2          WHEN 1 THEN pay_lv_13          WHEN 2 THEN pay_lv_24          WHEN 3 THEN pay_lv_35          ELSE 06         END) AS 'pay_cost'7 FROM  #t;

澳门新葡亰网址下载 8

 

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

澳门新葡亰网址下载 9

下面五个格局:CASE
WHEN和UNPIVOT函数能够用拼接SQL的格局来做,可是由于本人功力非常不够,写不出来

(3)游标

 作者不希罕使用游标,首要有多个原因

1、每便用的时候,要展开台式机看语法

2、占用能源

 笔者利用了下边包车型大巴sql语句来解决LZ的标题

澳门新葡亰网址下载 10澳门新葡亰网址下载 11

 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 INT10  --保存pay_level字段的值11 DECLARE @COUNT INT12  --保存#t1表的总行数值13 DECLARE @pay_lv INT14  --用于保存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 t120 21 22 --获取#t1表的总行数23 SELECT @COUNT = COUNT(*) FROM  [#temptb]24 WHILE @i <= @COUNT 25   BEGIN26     SELECT @pay_level = [pay_level] FROM  [#temptb] WHERE  id = @i27   --判断列名是否存在,不存在就插入028     IF 'pay_lv_' + CAST(@pay_level AS VARCHAR(200)) IN ( SELECT  NAME FROM   SYS.[syscolumns] ) 29       BEGIN30         --用拼接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 OUTPUT33         INSERT INTO #ttt VALUES (@pay_lv)34       END35     ELSE 36       BEGIN37         INSERT INTO #ttt VALUES(0)38       END39     SET @i = @i + 140   END41 42 43 44 SELECT A.[client], A.[pay_level], B.[pay_cost]45 FROM  [#temptb] AS A46 INNER JOIN [#ttt] AS B ON A.[ID] = B.[ID]47 ORDER BY A.[ID] ASC48 49 DROP TABLE [#temptb]50 DROP TABLE [#ttt]

View Code

本人这么些sql语句也亟需拼接sql来到达LZ想要的职能

只是那篇作品的主要不是拼接SQL


珍视是怎麽模仿游标

实际这些主意是最原始的办法,以前化解论坛难点的时候用过,想不到此次也能用上

要害代码有以下几句

 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 + 110 ----------------------------------11 SELECT A.[client], A.[pay_level], B.[pay_cost]12 FROM  [#temptb] AS A13 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

 

  
MS-SQL底蕴都学完了,以为本人支配的还不易,什么人知依旧难点一大堆,比方上述帖子正是。

  
话归正转,为消灭净尽该难题,大家得引入下SQL中的游标。笔者根基中并没有学到游标,假使您也未有学,这里给出八个学习它的网页,看完你就能用了:

1、)  

2、

3、其它SQL关机丛书上也许有有关章节。

那样通晓了游标的用处后,论坛里的不得了题目就化解了,这里给出作者的章程:

declare
mycu cursor

for
select * from 学生表

for
update

go

open
mycu

fetch
from mycu

update
学子表 set 自动编号=1 where current of mycu

delete
mycu

deallocate
mycu

运维,难点解决,happy。澳门新葡亰网址下载 12

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图