집

데이터 베이스

MySQL 튜토리얼

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:21 PM

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-

建表脚本：

<span> 1</span> <span>USE</span><span> tempdb
</span><span> 2</span> <span>GO</span>
<span> 3</span> 
<span> 4</span> <span>--</span><span>建表</span>
<span> 5</span> <span>CREATE</span> <span>TABLE</span><span> t1
</span><span> 6</span> <span>(
</span><span> 7</span>   client <span>VARCHAR</span>(<span>10</span><span>) ,
</span><span> 8</span>   pay_level <span>INT</span><span> ,
</span><span> 9</span>   pay_lv_1 <span>INT</span><span> ,
</span><span>10</span>   pay_lv_2 <span>INT</span><span> ,
</span><span>11</span>   pay_lv_3 <span>INT</span><span> ,
</span><span>12</span>   pay_lv_4 <span>INT</span><span> ,
</span><span>13</span>   pay_lv_5 <span>INT</span><span> ,
</span><span>14</span>   pay_lv_6 <span>INT</span><span> ,
</span><span>15</span>   pay_lv_7 <span>INT</span><span> ,
</span><span>16</span>   pay_lv_8 <span>INT</span><span> ,
</span><span>17</span>   pay_lv_9 <span>INT</span><span> ,
</span><span>18</span>   pay_lv_10 <span>INT</span><span> ,
</span><span>19</span>   pay_lv_11 <span>INT</span><span> ,
</span><span>20</span>   pay_lv_12 <span>INT</span><span> ,
</span><span>21</span>   pay_lv_13 <span>INT</span><span> ,
</span><span>22</span>   pay_lv_14 <span>INT</span><span> ,
</span><span>23</span>   pay_lv_15 <span>INT</span><span> ,
</span><span>24</span>   pay_lv_16 <span>INT</span><span> ,
</span><span>25</span>   pay_lv_17 <span>INT</span><span> ,
</span><span>26</span>   pay_lv_18 <span>INT</span><span> ,
</span><span>27</span>   pay_lv_19 <span>INT</span><span> ,
</span><span>28</span>   pay_lv_20 <span>INT</span><span> ,
</span><span>29</span>   pay_lv_21 <span>INT</span><span> ,
</span><span>30</span>   pay_lv_22 <span>INT</span><span> ,
</span><span>31</span>   pay_lv_23 <span>INT</span><span> ,
</span><span>32</span>   pay_lv_24 <span>INT</span><span> ,
</span><span>33</span>   pay_lv_25 <span>INT</span><span>,
</span><span>34</span> <span>);
</span><span>35</span> 
<span>36</span> 
<span>37</span> <span>--</span><span>插入测试数据</span>
<span>38</span> <span>DECLARE</span> <span>@i</span> <span>INT</span>
<span>39</span> <span>SET</span> <span>@i</span> <span>=</span> <span>1</span>
<span>40</span> <span>WHILE</span> <span>@i</span> <span> <span>8</span> 
<span>41</span>     <span>BEGIN</span>
<span>42</span>         <span>INSERT</span>  <span>INTO</span><span> t1 ( client, pay_level, pay_lv_1, pay_lv_2, pay_lv_3,
</span><span>43</span> <span>                           pay_lv_4, pay_lv_5, pay_lv_6, pay_lv_7, pay_lv_8,
</span><span>44</span> <span>                           pay_lv_9, pay_lv_10, pay_lv_11, pay_lv_12,
</span><span>45</span> <span>                           pay_lv_13, pay_lv_14, pay_lv_15, pay_lv_16,
</span><span>46</span> <span>                           pay_lv_17, pay_lv_18, pay_lv_19, pay_lv_20,
</span><span>47</span> <span>                           pay_lv_21, pay_lv_22, pay_lv_23, pay_lv_24,
</span><span>48</span> <span>                           pay_lv_25 )
</span><span>49</span>                 <span>SELECT</span>  <span>'</span><span>client</span><span>'</span> <span>+</span> <span>CAST</span>(<span>@i</span> <span>AS</span> <span>VARCHAR</span>(<span>10</span><span>)),
</span><span>50</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span>51</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span>52</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span>53</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span>54</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span>55</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span>56</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span>57</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span>58</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span>59</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span>60</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span>61</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span>62</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>()
</span><span>63</span>           <span>SET</span> <span>@i</span><span>=</span><span>@i</span><span>+</span><span>1</span>
<span>64</span> 
<span>65</span>     <span>END</span>
<span>66</span> 
<span>67</span> <span>SELECT</span> <span>*</span> <span>FROM</span><span> t1
</span><span>68</span> <span>GO</span></span>

로그인 후 복사

View Code

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

图1

LZ说原表就是类似上面那样，实际表中pay_lv_会有很多列至少100列，我这里为了测试只建了25个pay_lv_列

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

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

图2

client列和pay_level列不变，增加一个pay_cost列

pay_cost列根据pay_level列的值去取pay_lv_列的值，或者我用下面的图片会更加明白

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

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

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

<span> 1</span> <span>USE</span><span> tempdb
</span><span> 2</span> <span>GO</span>
<span> 3</span> 
<span> 4</span> 
<span> 5</span> <span>CREATE</span> <span>TABLE</span><span> #t
</span><span> 6</span> <span>(
</span><span> 7</span>   client <span>VARCHAR</span>(<span>10</span><span>) ,
</span><span> 8</span>   pay_level <span>INT</span><span> ,
</span><span> 9</span>   pay_lv_1 <span>INT</span><span> ,
</span><span>10</span>   pay_lv_2 <span>INT</span><span> ,
</span><span>11</span>   pay_lv_3 <span>INT</span>
<span>12</span> <span>);
</span><span>13</span> 
<span>14</span> <span>INSERT</span> <span>INTO</span><span> #t ( client ,
</span><span>15</span> <span>          pay_level ,
</span><span>16</span> <span>          pay_lv_1 ,
</span><span>17</span> <span>          pay_lv_2 ,
</span><span>18</span> <span>          pay_lv_3
</span><span>19</span> <span>        )
</span><span>20</span> <span>VALUES</span>  ( <span>'</span><span>client1</span><span>'</span> , <span>--</span><span> client - varchar(10)</span>
<span>21</span>           <span>1</span>, <span>--</span><span> pay_level - int</span>
<span>22</span>           <span>10</span> , <span>--</span><span> pay_lv_1 - int</span>
<span>23</span>           <span>12</span> , <span>--</span><span> pay_lv_2 - int</span>
<span>24</span>           <span>14</span>  <span>--</span><span> pay_lv_3 - int</span>
<span>25</span> <span>        )
</span><span>26</span> 
<span>27</span> 
<span>28</span> <span>INSERT</span> <span>INTO</span><span> #t ( client ,
</span><span>29</span> <span>          pay_level ,
</span><span>30</span> <span>          pay_lv_1 ,
</span><span>31</span> <span>          pay_lv_2 ,
</span><span>32</span> <span>          pay_lv_3
</span><span>33</span> <span>        )
</span><span>34</span> <span>VALUES</span>  ( <span>'</span><span>client2</span><span>'</span> , <span>--</span><span> client - varchar(10)</span>
<span>35</span>           <span>3</span>, <span>--</span><span> pay_level - int</span>
<span>36</span>           <span>21</span> , <span>--</span><span> pay_lv_1 - int</span>
<span>37</span>           <span>22</span> , <span>--</span><span> pay_lv_2 - int</span>
<span>38</span>           <span>23</span>  <span>--</span><span> pay_lv_3 - int</span>
<span>39</span> <span>        )
</span><span>40</span> 
<span>41</span> <span>INSERT</span> <span>INTO</span><span> #t ( client ,
</span><span>42</span> <span>          pay_level ,
</span><span>43</span> <span>          pay_lv_1 ,
</span><span>44</span> <span>          pay_lv_2 ,
</span><span>45</span> <span>          pay_lv_3
</span><span>46</span> <span>        )
</span><span>47</span> <span>VALUES</span>  ( <span>'</span><span>client3</span><span>'</span> , <span>--</span><span> client - varchar(10)</span>
<span>48</span>           <span>2</span>, <span>--</span><span> pay_level - int</span>
<span>49</span>           <span>30</span> , <span>--</span><span> pay_lv_1 - int</span>
<span>50</span>           <span>32</span> , <span>--</span><span> pay_lv_2 - int</span>
<span>51</span>           <span>33</span>  <span>--</span><span> pay_lv_3 - int</span>
<span>52</span> <span>        )
</span><span>53</span> 
<span>54</span> <span>SELECT</span> <span>*</span> <span>FROM</span> #t

로그인 후 복사

View Code

（1）case when

<span>1</span> <span>SELECT</span>  client,<span>[</span><span>pay_level</span><span>]</span>,( <span>CASE</span><span> pay_level
</span><span>2</span>                   <span>WHEN</span> <span>1</span> <span>THEN</span><span> pay_lv_1
</span><span>3</span>                   <span>WHEN</span> <span>2</span> <span>THEN</span><span> pay_lv_2
</span><span>4</span>                   <span>WHEN</span> <span>3</span> <span>THEN</span><span> pay_lv_3
</span><span>5</span>                   <span>ELSE</span> <span>0</span>
<span>6</span>                 <span>END</span>) <span>AS</span> <span>'</span><span>pay_cost</span><span>'</span>
<span>7</span> <span>FROM</span>    #t;

로그인 후 복사

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

图4

（2）UNPIVOT函数

<span> 1</span> <span>SELECT</span>  <span>*</span> <span>INTO</span><span> #tt
</span><span> 2</span> <span>FROM</span>    ( <span>SELECT</span>    <span>*</span>
<span> 3</span>           <span>FROM</span><span>      #t
</span><span> 4</span> <span>        ) p UNPIVOT
</span><span> 5</span>    ( pay_cost <span>FOR</span> pay_lv <span>IN</span> ( pay_lv_1, pay_lv_2, pay_lv_3 ) )<span>AS</span><span> unpvt
</span><span> 6</span> <span>WHERE</span>   <span>CAST</span>(<span>RIGHT</span>(pay_lv, <span>1</span>) <span>AS</span> <span>INT</span>) <span>=</span><span> pay_level
</span><span> 7</span> 
<span> 8</span> <span>SELECT</span> <span>[</span><span>client</span><span>]</span>,<span>[</span><span>pay_level</span><span>]</span>,<span>[</span><span>pay_cost</span><span>]</span> <span>FROM</span> <span>[</span><span>#tt</span><span>]</span>
<span> 9</span> 
<span>10</span> <span>DROP</span> <span>TABLE</span> <span>[</span><span>#tt</span><span>]</span>

로그인 후 복사

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

图5

上面两个方法：CASE WHEN和UNPIVOT函数可以用拼接SQL的方法来做，不过由于本人功力不够，写不出来

（3）游标

我不喜欢使用游标，主要有两个原因

1、每次用的时候，要打开笔记本看语法

2、占用资源

我使用了下面的sql语句来解决LZ的问题

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

<span> 1</span> <span>IF</span> <span>object_id</span>(<span>'</span><span>#ttt</span><span>'</span>) <span>IS</span> <span>NOT</span> <span>NULL</span>
<span> 2</span> <span>DROP</span> <span>TABLE</span><span> #ttt
</span><span> 3</span> <span>IF</span> <span>object_id</span>(<span>'</span><span>#temptb</span><span>'</span>) <span>IS</span> <span>NOT</span> <span>NULL</span>
<span> 4</span> <span>DROP</span> <span>TABLE</span><span> #temptb
</span><span> 5</span> 
<span> 6</span> <span>DECLARE</span> <span>@i</span> <span>INT</span>
<span> 7</span>   <span>--</span><span>用于循环的</span>
<span> 8</span> <span>SET</span> <span>@i</span> <span>=</span> <span>1</span>
<span> 9</span> <span>DECLARE</span> <span>@pay_level</span> <span>INT</span>
<span>10</span>   <span>--</span><span>保存pay_level字段的值</span>
<span>11</span> <span>DECLARE</span> <span>@COUNT</span> <span>INT</span>
<span>12</span>    <span>--</span><span>保存#t1表的总行数值</span>
<span>13</span> <span>DECLARE</span> <span>@pay_lv</span> <span>INT</span>
<span>14</span>   <span>--</span><span>用于保存pay_lv的值</span>
<span>15</span> <span>DECLARE</span> <span>@sql</span> <span>NVARCHAR</span>(<span>2000</span><span>)
</span><span>16</span> 
<span>17</span> <span>CREATE</span> <span>TABLE</span> #ttt (ID <span>INT</span> <span>IDENTITY</span>(<span>1</span>,<span>1</span>), pay_cost <span>INT</span><span> )
</span><span>18</span> 
<span>19</span> <span>SELECT</span>  <span>IDENTITY</span>( <span>INT</span>,<span>1</span>,<span>1</span> ) <span>AS</span> ID, <span>*</span> <span>INTO</span>    #temptb <span>FROM</span><span>  t1
</span><span>20</span> 
<span>21</span> 
<span>22</span> <span>--</span><span>获取#t1表的总行数</span>
<span>23</span> <span>SELECT</span>  <span>@COUNT</span> <span>=</span> <span>COUNT</span>(<span>*</span>) <span>FROM</span>    <span>[</span><span>#temptb</span><span>]</span>
<span>24</span> <span>WHILE</span> <span>@i</span> <span> <span>@COUNT</span> 
<span>25</span>     <span>BEGIN</span>
<span>26</span>         <span>SELECT</span>  <span>@pay_level</span> <span>=</span> <span>[</span><span>pay_level</span><span>]</span> <span>FROM</span>    <span>[</span><span>#temptb</span><span>]</span> <span>WHERE</span>   id <span>=</span> <span>@i</span>
<span>27</span>     <span>--</span><span>判断列名是否存在，不存在就插入0</span>
<span>28</span>         <span>IF</span> <span>'</span><span>pay_lv_</span><span>'</span> <span>+</span> <span>CAST</span>(<span>@pay_level</span> <span>AS</span> <span>VARCHAR</span>(<span>200</span>)) <span>IN</span> ( <span>SELECT</span>   NAME <span>FROM</span>     SYS.<span>[</span><span>syscolumns</span><span>]</span><span> ) 
</span><span>29</span>             <span>BEGIN</span>
<span>30</span>                 <span>--</span><span>用拼接sql的方法来获得pay_lv列对应的值，然后插入到#ttt表</span>
<span>31</span>                 <span>SET</span> <span>@sql</span> <span>=</span> N<span>'</span><span>select </span><span>'</span> <span>+</span> <span>'</span><span> @pay_lv=pay_lv_</span><span>'</span> <span>+</span> <span>CAST</span>(<span>@pay_level</span> <span>AS</span> <span>NVARCHAR</span>(<span>200</span>)) <span>+</span> <span>'</span><span> from #temptb where id=</span><span>'</span> <span>+</span> <span>CAST</span>(<span>@i</span> <span>AS</span> <span>NVARCHAR</span>(<span>20</span><span>))
</span><span>32</span>                 <span>EXEC</span> sp_executesql <span>@sql</span>, N<span>'</span><span>@pay_lv   int   output </span><span>'</span>, <span>@pay_lv</span><span> OUTPUT
</span><span>33</span>                 <span>INSERT</span>  <span>INTO</span> #ttt <span>VALUES</span>  (<span>@pay_lv</span><span>)
</span><span>34</span>             <span>END</span>
<span>35</span>         <span>ELSE</span> 
<span>36</span>             <span>BEGIN</span>
<span>37</span>                 <span>INSERT</span>  <span>INTO</span> #ttt <span>VALUES</span>(<span>0</span><span>)
</span><span>38</span>             <span>END</span>
<span>39</span>         <span>SET</span> <span>@i</span> <span>=</span> <span>@i</span> <span>+</span> <span>1</span>
<span>40</span>     <span>END</span>
<span>41</span> 
<span>42</span> 
<span>43</span> 
<span>44</span> <span>SELECT</span>  A.<span>[</span><span>client</span><span>]</span>, A.<span>[</span><span>pay_level</span><span>]</span>, B.<span>[</span><span>pay_cost</span><span>]</span>
<span>45</span> <span>FROM</span>    <span>[</span><span>#temptb</span><span>]</span> <span>AS</span><span> A
</span><span>46</span> <span>INNER</span> <span>JOIN</span> <span>[</span><span>#ttt</span><span>]</span> <span>AS</span> B <span>ON</span> A.<span>[</span><span>ID</span><span>]</span> <span>=</span> B.<span>[</span><span>ID</span><span>]</span>
<span>47</span> <span>ORDER</span> <span>BY</span> A.<span>[</span><span>ID</span><span>]</span> <span>ASC</span>
<span>48</span> 
<span>49</span> <span>DROP</span> <span>TABLE</span> <span>[</span><span>#temptb</span><span>]</span>
<span>50</span> <span>DROP</span> <span>TABLE</span> <span>[</span><span>#ttt</span><span>]</span></span>

로그인 후 복사

View Code

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

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

重点是怎麽模仿游标

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

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

<span>  1</span> <span>USE</span><span> tempdb
</span><span>  2</span> <span>GO</span>
<span>  3</span> 
<span>  4</span> <span>--</span><span>建表</span>
<span>  5</span> <span>CREATE</span> <span>TABLE</span><span> t1
</span><span>  6</span> <span>(
</span><span>  7</span>   client <span>VARCHAR</span>(<span>10</span><span>) ,
</span><span>  8</span>   pay_level <span>INT</span><span> ,
</span><span>  9</span>   pay_lv_1 <span>INT</span><span> ,
</span><span> 10</span>   pay_lv_2 <span>INT</span><span> ,
</span><span> 11</span>   pay_lv_3 <span>INT</span><span> ,
</span><span> 12</span>   pay_lv_4 <span>INT</span><span> ,
</span><span> 13</span>   pay_lv_5 <span>INT</span><span> ,
</span><span> 14</span>   pay_lv_6 <span>INT</span><span> ,
</span><span> 15</span>   pay_lv_7 <span>INT</span><span> ,
</span><span> 16</span>   pay_lv_8 <span>INT</span><span> ,
</span><span> 17</span>   pay_lv_9 <span>INT</span><span> ,
</span><span> 18</span>   pay_lv_10 <span>INT</span><span> ,
</span><span> 19</span>   pay_lv_11 <span>INT</span><span> ,
</span><span> 20</span>   pay_lv_12 <span>INT</span><span> ,
</span><span> 21</span>   pay_lv_13 <span>INT</span><span> ,
</span><span> 22</span>   pay_lv_14 <span>INT</span><span> ,
</span><span> 23</span>   pay_lv_15 <span>INT</span><span> ,
</span><span> 24</span>   pay_lv_16 <span>INT</span><span> ,
</span><span> 25</span>   pay_lv_17 <span>INT</span><span> ,
</span><span> 26</span>   pay_lv_18 <span>INT</span><span> ,
</span><span> 27</span>   pay_lv_19 <span>INT</span><span> ,
</span><span> 28</span>   pay_lv_20 <span>INT</span><span> ,
</span><span> 29</span>   pay_lv_21 <span>INT</span><span> ,
</span><span> 30</span>   pay_lv_22 <span>INT</span><span> ,
</span><span> 31</span>   pay_lv_23 <span>INT</span><span> ,
</span><span> 32</span>   pay_lv_24 <span>INT</span><span> ,
</span><span> 33</span>   pay_lv_25 <span>INT</span><span>,
</span><span> 34</span> <span>);
</span><span> 35</span> 
<span> 36</span> 
<span> 37</span> <span>--</span><span>插入测试数据</span>
<span> 38</span> <span>DECLARE</span> <span>@i</span> <span>INT</span>
<span> 39</span> <span>SET</span> <span>@i</span> <span>=</span> <span>1</span>
<span> 40</span> <span>WHILE</span> <span>@i</span> <span> <span>8</span> 
<span> 41</span>     <span>BEGIN</span>
<span> 42</span>         <span>INSERT</span>  <span>INTO</span><span> t1 ( client, pay_level, pay_lv_1, pay_lv_2, pay_lv_3,
</span><span> 43</span> <span>                           pay_lv_4, pay_lv_5, pay_lv_6, pay_lv_7, pay_lv_8,
</span><span> 44</span> <span>                           pay_lv_9, pay_lv_10, pay_lv_11, pay_lv_12,
</span><span> 45</span> <span>                           pay_lv_13, pay_lv_14, pay_lv_15, pay_lv_16,
</span><span> 46</span> <span>                           pay_lv_17, pay_lv_18, pay_lv_19, pay_lv_20,
</span><span> 47</span> <span>                           pay_lv_21, pay_lv_22, pay_lv_23, pay_lv_24,
</span><span> 48</span> <span>                           pay_lv_25 )
</span><span> 49</span>                 <span>SELECT</span>  <span>'</span><span>client</span><span>'</span> <span>+</span> <span>CAST</span>(<span>@i</span> <span>AS</span> <span>VARCHAR</span>(<span>10</span><span>)),
</span><span> 50</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span> 51</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span> 52</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span> 53</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span> 54</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span> 55</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span> 56</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span> 57</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span> 58</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span> 59</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span> 60</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span> 61</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>(),
</span><span> 62</span>                         ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span>(), ( <span>20</span> <span>+</span> <span>1</span> ) <span>*</span> <span>RAND</span><span>()
</span><span> 63</span>           <span>SET</span> <span>@i</span><span>=</span><span>@i</span><span>+</span><span>1</span>
<span> 64</span> 
<span> 65</span>     <span>END</span>
<span> 66</span> 
<span> 67</span> <span>SELECT</span> <span>*</span> <span>FROM</span><span> t1
</span><span> 68</span> <span>GO</span>
<span> 69</span> 
<span> 70</span> <span>--</span><span>ALTER TABLE [t1] DROP COLUMN [pay_lv_2]</span>
<span> 71</span> 
<span> 72</span> 
<span> 73</span> <span>--</span><span>---------------------------------------------------</span>
<span> 74</span> <span>IF</span> <span>object_id</span>(<span>'</span><span>#ttt</span><span>'</span>) <span>IS</span> <span>NOT</span> <span>NULL</span>
<span> 75</span> <span>DROP</span> <span>TABLE</span><span> #ttt
</span><span> 76</span> <span>IF</span> <span>object_id</span>(<span>'</span><span>#temptb</span><span>'</span>) <span>IS</span> <span>NOT</span> <span>NULL</span>
<span> 77</span> <span>DROP</span> <span>TABLE</span><span> #temptb
</span><span> 78</span> 
<span> 79</span> <span>DECLARE</span> <span>@i</span> <span>INT</span>
<span> 80</span>   <span>--</span><span>用于循环的</span>
<span> 81</span> <span>SET</span> <span>@i</span> <span>=</span> <span>1</span>
<span> 82</span> <span>DECLARE</span> <span>@pay_level</span> <span>INT</span>
<span> 83</span>   <span>--</span><span>保存pay_level字段的值</span>
<span> 84</span> <span>DECLARE</span> <span>@COUNT</span> <span>INT</span>
<span> 85</span>    <span>--</span><span>保存t1表的总行数值</span>
<span> 86</span> <span>DECLARE</span> <span>@pay_lv</span> <span>INT</span>
<span> 87</span>   <span>--</span><span>用于保存pay_lv的值</span>
<span> 88</span> <span>DECLARE</span> <span>@sql</span> <span>NVARCHAR</span>(<span>2000</span><span>)
</span><span> 89</span> 
<span> 90</span> <span>CREATE</span> <span>TABLE</span> #ttt (ID <span>INT</span> <span>IDENTITY</span>(<span>1</span>,<span>1</span>), pay_cost <span>INT</span><span> )
</span><span> 91</span> 
<span> 92</span> <span>SELECT</span>  <span>IDENTITY</span>( <span>INT</span>,<span>1</span>,<span>1</span> ) <span>AS</span> ID, <span>*</span> <span>INTO</span>    #temptb <span>FROM</span><span>  t1
</span><span> 93</span> 
<span> 94</span> 
<span> 95</span> <span>--</span><span>获取t1表的总行数</span>
<span> 96</span> <span>SELECT</span>  <span>@COUNT</span> <span>=</span> <span>COUNT</span>(<span>*</span>) <span>FROM</span>    <span>[</span><span>#temptb</span><span>]</span>
<span> 97</span> <span>WHILE</span> <span>@i</span> <span> <span>@COUNT</span> 
<span> 98</span>     <span>BEGIN</span>
<span> 99</span>         <span>SELECT</span>  <span>@pay_level</span> <span>=</span> <span>[</span><span>pay_level</span><span>]</span> <span>FROM</span>    <span>[</span><span>#temptb</span><span>]</span> <span>WHERE</span>   id <span>=</span> <span>@i</span>
<span>100</span>     <span>--</span><span>判断列名是否存在，不存在就插入0</span>
<span>101</span>         <span>IF</span> <span>'</span><span>pay_lv_</span><span>'</span> <span>+</span> <span>CAST</span>(<span>@pay_level</span> <span>AS</span> <span>VARCHAR</span>(<span>200</span>)) <span>IN</span> ( <span>SELECT</span>   NAME <span>FROM</span>     SYS.<span>[</span><span>syscolumns</span><span>]</span><span> ) 
</span><span>102</span>             <span>BEGIN</span>
<span>103</span>                 <span>--</span><span>用拼接sql的方法来获得pay_lv列对应的值，然后插入到#ttt表</span>
<span>104</span>                 <span>SET</span> <span>@sql</span> <span>=</span> N<span>'</span><span>select </span><span>'</span> <span>+</span> <span>'</span><span> @pay_lv=pay_lv_</span><span>'</span> <span>+</span> <span>CAST</span>(<span>@pay_level</span> <span>AS</span> <span>NVARCHAR</span>(<span>200</span>)) <span>+</span> <span>'</span><span> from #temptb where id=</span><span>'</span> <span>+</span> <span>CAST</span>(<span>@i</span> <span>AS</span> <span>NVARCHAR</span>(<span>20</span><span>))
</span><span>105</span>                 <span>EXEC</span> sp_executesql <span>@sql</span>, N<span>'</span><span>@pay_lv   int   output </span><span>'</span>, <span>@pay_lv</span><span> OUTPUT
</span><span>106</span>                 <span>INSERT</span>  <span>INTO</span> #ttt <span>VALUES</span>  (<span>@pay_lv</span><span>)
</span><span>107</span>             <span>END</span>
<span>108</span>         <span>ELSE</span> 
<span>109</span>             <span>BEGIN</span>
<span>110</span>                 <span>INSERT</span>  <span>INTO</span> #ttt <span>VALUES</span>(<span>0</span><span>)
</span><span>111</span>             <span>END</span>
<span>112</span>         <span>SET</span> <span>@i</span> <span>=</span> <span>@i</span> <span>+</span> <span>1</span>
<span>113</span>     <span>END</span>
<span>114</span> 
<span>115</span> 
<span>116</span> 
<span>117</span> <span>SELECT</span>  A.<span>[</span><span>client</span><span>]</span>, A.<span>[</span><span>pay_level</span><span>]</span>, B.<span>[</span><span>pay_cost</span><span>]</span>
<span>118</span> <span>FROM</span>    <span>[</span><span>#temptb</span><span>]</span> <span>AS</span><span> A
</span><span>119</span> <span>INNER</span> <span>JOIN</span> <span>[</span><span>#ttt</span><span>]</span> <span>AS</span> B <span>ON</span> A.<span>[</span><span>ID</span><span>]</span> <span>=</span> B.<span>[</span><span>ID</span><span>]</span>
<span>120</span> <span>ORDER</span> <span>BY</span> A.<span>[</span><span>ID</span><span>]</span> <span>ASC</span>
<span>121</span> 
<span>122</span> <span>DROP</span> <span>TABLE</span> <span>[</span><span>#temptb</span><span>]</span>
<span>123</span> <span>DROP</span> <span>TABLE</span> <span>[</span><span>#ttt</span><span>]</span></span></span>

로그인 후 복사

View Code

关键代码有以下几句

<span> 1</span> <span>CREATE</span> <span>TABLE</span> #ttt (ID <span>INT</span> <span>IDENTITY</span>(<span>1</span>,<span>1</span>), pay_cost <span>INT</span><span> )
</span><span> 2</span> 
<span> 3</span> <span>SELECT</span>  <span>IDENTITY</span>( <span>INT</span>,<span>1</span>,<span>1</span> ) <span>AS</span> ID, <span>*</span> <span>INTO</span>    #temptb <span>FROM</span><span>  t1
</span><span> 4</span> 
<span> 5</span> <span>--</span><span>获取#t1表的总行数</span>
<span> 6</span> <span>SELECT</span>  <span>@COUNT</span> <span>=</span> <span>COUNT</span>(<span>*</span>) <span>FROM</span>    <span>[</span><span>#temptb</span><span>]</span>
<span> 7</span> <span>WHILE</span> <span>@i</span> <span> <span>@COUNT</span> 
<span> 8</span> <span>SELECT</span>  <span>@pay_level</span> <span>=</span> <span>[</span><span>pay_level</span><span>]</span> <span>FROM</span>    <span>[</span><span>#temptb</span><span>]</span> <span>WHERE</span>   id <span>=</span> <span>@i</span>
<span> 9</span> <span>SET</span> <span>@i</span> <span>=</span> <span>@i</span> <span>+</span> <span>1</span>
<span>10</span> <span>--</span><span>--------------------------------</span>
<span>11</span> <span>SELECT</span>  A.<span>[</span><span>client</span><span>]</span>, A.<span>[</span><span>pay_level</span><span>]</span>, B.<span>[</span><span>pay_cost</span><span>]</span>
<span>12</span> <span>FROM</span>    <span>[</span><span>#temptb</span><span>]</span> <span>AS</span><span> A
</span><span>13</span> <span>INNER</span> <span>JOIN</span> <span>[</span><span>#ttt</span><span>]</span> <span>AS</span> B <span>ON</span> A.<span>[</span><span>ID</span><span>]</span> <span>=</span> B.<span>[</span><span>ID</span><span>]</span>
<span>14</span> <span>ORDER</span> <span>BY</span> A.<span>[</span><span>ID</span><span>]</span> <span>ASC</span></span>

로그인 후 복사

原表是没有自增id的，我建一个临时表#temptb，临时表有一个自增id，并把原表的数据全部放入临时表

获取临时表的行数，用于循环

每次执行的时候根据 WHERE id = @i 来逐行逐行获取值，变量@i每次循环都递增1

将获取到的值都插入到#ttt这个临时表里面，然后根据ID的值做两表连接就可以得到LZ的结果

我说的无中生有就是“在原表里增加一个自增id方便循环，既简单又容易理解o(∩_∩)o ”

判断

我这里还用了一句

<span>1</span> <span>IF</span> <span>'</span><span>pay_lv_</span><span>'</span> <span>+</span> <span>CAST</span>(<span>@pay_level</span> <span>AS</span> <span>VARCHAR</span>(<span>200</span>)) <span>IN</span> ( <span>SELECT</span>   NAME <span>FROM</span>     SYS.<span>[</span><span>syscolumns</span><span>]</span> )

로그인 후 복사

用于判断要获取值的pay_lv_列是否存在，如果存在就插入pay_lv_列的值，如果不存在就插入0

总结

其实如果觉得某样东西很难去实现，能不能用一个变通的方法呢？多动脑筋，办法会有的

如有不对的地方，欢迎大家拍砖o(∩_∩)o

본 웹사이트의 성명

본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.

핫 AI 도구

뜨거운 도구

뜨거운 주제

Gmail 이메일의 로그인 입구는 어디에 있나요?

7321

자바 튜토리얼

1625

Cakephp 튜토리얼

1349

라라벨 튜토리얼

1261

PHP 튜토리얼

1209

Related knowledge

sqlserver 데이터베이스에 이름이 지정된 개체가 이미 존재하는 문제를 해결하는 방법 Apr 05, 2024 pm 09:42 PM

SQL Server 데이터베이스에 이미 존재하는 동일한 이름을 가진 개체의 경우 다음 단계를 수행해야 합니다. 개체 유형(테이블, 뷰, 저장 프로시저)을 확인합니다. IF NOT EXISTS를 사용하면 객체가 비어 있는 경우 생성을 건너뛸 수 있습니다. 개체에 데이터가 있는 경우 다른 이름을 사용하거나 구조를 수정하세요. 기존 개체를 삭제하려면 DROP을 사용하세요. 주의하세요. 백업을 권장합니다. 삭제되거나 이름이 바뀐 개체에 대한 참조가 없는지 확인하려면 스키마 변경 사항을 확인하세요.

mdf 파일을 sqlserver로 가져오는 방법 Apr 08, 2024 am 11:41 AM

가져오기 단계는 다음과 같습니다. MDF 파일을 SQL Server의 데이터 디렉터리(일반적으로 C:\Program Files\Microsoft SQL Server\MSSQL\DATA)에 복사합니다. SSMS(SQL Server Management Studio)에서 데이터베이스를 열고 연결을 선택합니다. 추가 버튼을 클릭하고 MDF 파일을 선택합니다. 데이터베이스 이름을 확인하고 확인 버튼을 클릭합니다.

sqlserver 서비스를 시작할 수 없는 경우 수행할 작업 Apr 05, 2024 pm 10:00 PM

SQL Server 서비스가 시작되지 않는 경우 해결해야 할 몇 가지 단계는 다음과 같습니다. 오류 로그를 확인하여 근본 원인을 확인합니다. 서비스 계정에 서비스를 시작할 수 있는 권한이 있는지 확인하세요. 종속성 서비스가 실행 중인지 확인하세요. 바이러스 백신 소프트웨어를 비활성화합니다. SQL Server 설치를 복구합니다. 복구가 작동하지 않으면 SQL Server를 다시 설치하십시오.

sqlserver 포트번호 확인하는 방법 Apr 05, 2024 pm 09:57 PM

SQL Server 포트 번호를 보려면 SSMS를 열고 서버에 연결합니다. 개체 탐색기에서 서버 이름을 찾아 마우스 오른쪽 단추로 클릭하고 속성을 선택합니다. 연결 탭에서 TCP 포트 필드를 확인하세요.

sqlserver에서 실수로 삭제한 데이터베이스를 복구하는 방법 Apr 05, 2024 pm 10:39 PM

실수로 SQL Server 데이터베이스를 삭제한 경우 다음 단계를 수행하여 복구할 수 있습니다. 데이터베이스 활동 중지, 데이터베이스 로그 확인, 백업에서 복원, DBCC CHECKDB 사용 파티 도구. 데이터 손실을 방지하려면 데이터베이스를 정기적으로 백업하고 트랜잭션 로깅을 활성화하십시오.

sqlserver 데이터베이스는 어디에 있나요? Apr 05, 2024 pm 08:21 PM

SQL Server 데이터베이스 파일은 일반적으로 다음 기본 위치에 저장됩니다. Windows: C:\Program Files\Microsoft SQL Server\MSSQL\DATALinux: /var/opt/mssql/data 데이터베이스 파일 경로를 수정하여 데이터베이스 파일 위치를 사용자 정의할 수 있습니다. 환경.

설치에 실패하면 sqlserver를 삭제하는 방법은 무엇입니까? Apr 05, 2024 pm 11:27 PM

SQL Server 설치가 실패하면 다음 단계에 따라 정리할 수 있습니다. SQL Server 제거 레지스트리 키 삭제 파일 및 폴더 삭제 컴퓨터를 다시 시작합니다.

Java 연결 SqlServer 오류를 해결하는 방법 May 01, 2023 am 09:22 AM

이번에는 이전에 사용하지 않았던 SqlServer 데이터베이스를 사용하고 있었는데 문제가 심각하지 않다는 것을 발견하였고, 요구사항 문서의 단계에 따라 SqlServer를 연결한 후 SpringBoot 프로젝트를 시작하여 찾았습니다. 처음에는 SQLServer 연결에 문제가 있는 줄 알고 데이터베이스를 확인하러 갔는데, 데이터베이스에 있는 모든 것이 정상인지 먼저 동료들에게 그런 문제가 있는지 물어봤습니다. 그렇지 않다는 것을 알았기 때문에 저는 Baidu 프로그래밍을 시작했습니다. 제가 해결하기 시작한 구체적인 오류 메시지는 이것이었기 때문에 Baidu 오류 보고를 시작했습니다: ERRORc.a.d.p.DruidDataSource$CreateCo

See all articles

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

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

핫 AI 도구

Undresser.AI Undress

AI Clothes Remover

Undress AI Tool

Clothoff.io

AI Hentai Generator

인기 기사

뜨거운 도구

메모장++7.3.1

SublimeText3 중국어 버전

스튜디오 13.0.1 보내기

드림위버 CS6

SublimeText3 Mac 버전

뜨거운 주제