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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:21 PM

sqlserversubstitutecursor

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

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

MySQL's Place: Databases and ProgrammingApr 13, 2025 am 12:18 AM

MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen

MySQL: From Small Businesses to Large EnterprisesApr 13, 2025 am 12:17 AM

MySQL is suitable for small and large enterprises. 1) Small businesses can use MySQL for basic data management, such as storing customer information. 2) Large enterprises can use MySQL to process massive data and complex business logic to optimize query performance and transaction processing.

What are phantom reads and how does InnoDB prevent them (Next-Key Locking)?Apr 13, 2025 am 12:16 AM

InnoDB effectively prevents phantom reading through Next-KeyLocking mechanism. 1) Next-KeyLocking combines row lock and gap lock to lock records and their gaps to prevent new records from being inserted. 2) In practical applications, by optimizing query and adjusting isolation levels, lock competition can be reduced and concurrency performance can be improved.

MySQL: Not a Programming Language, But...Apr 13, 2025 am 12:03 AM

MySQL is not a programming language, but its query language SQL has the characteristics of a programming language: 1. SQL supports conditional judgment, loops and variable operations; 2. Through stored procedures, triggers and functions, users can perform complex logical operations in the database.

MySQL: An Introduction to the World's Most Popular DatabaseApr 12, 2025 am 12:18 AM

MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.

The Importance of MySQL: Data Storage and ManagementApr 12, 2025 am 12:18 AM

MySQL is an open source relational database management system suitable for data storage, management, query and security. 1. It supports a variety of operating systems and is widely used in Web applications and other fields. 2. Through the client-server architecture and different storage engines, MySQL processes data efficiently. 3. Basic usage includes creating databases and tables, inserting, querying and updating data. 4. Advanced usage involves complex queries and stored procedures. 5. Common errors can be debugged through the EXPLAIN statement. 6. Performance optimization includes the rational use of indexes and optimized query statements.

Why Use MySQL? Benefits and AdvantagesApr 12, 2025 am 12:17 AM

MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.

Describe InnoDB locking mechanisms (shared locks, exclusive locks, intention locks, record locks, gap locks, next-key locks).Apr 12, 2025 am 12:16 AM

InnoDB's lock mechanisms include shared locks, exclusive locks, intention locks, record locks, gap locks and next key locks. 1. Shared lock allows transactions to read data without preventing other transactions from reading. 2. Exclusive lock prevents other transactions from reading and modifying data. 3. Intention lock optimizes lock efficiency. 4. Record lock lock index record. 5. Gap lock locks index recording gap. 6. The next key lock is a combination of record lock and gap lock to ensure data consistency.

See all articles

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)

3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

R.E.P.O. Best Graphic Settings

3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Assassin's Creed Shadows: Seashell Riddle Solution

2 weeks agoByDDD

R.E.P.O. How to Fix Audio if You Can't Hear Anyone

3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

WWE 2K25: How To Unlock Everything In MyRise

4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Atom editor mac version download

The most popular open source editor

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

Dreamweaver CS6

Visual web development tools

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

Hot Topics

Where is the login entrance for gmail email?

7488

CakePHP Tutorial

1377

What is the format of the account name of steam

win11 activation key permanent

nyt connections hints and answers