Maison

base de données

tutoriel 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>

Copier après la connexion

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

Copier après la connexion

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;

Copier après la connexion

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>

Copier après la connexion

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>

Copier après la connexion

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>

Copier après la connexion

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>

Copier après la connexion

原表是没有自增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> )

Copier après la connexion

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

总结

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

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

Déclaration de ce site Web

Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn

Outils d'IA chauds

Undresser.AI Undress

Application basée sur l'IA pour créer des photos de nu réalistes

AI Clothes Remover

Outil d'IA en ligne pour supprimer les vêtements des photos.

Undress AI Tool

Images de déshabillage gratuites

Clothoff.io

Dissolvant de vêtements AI

AI Hentai Generator

Générez AI Hentai gratuitement.

Afficher plus

Article chaud

R.E.P.O. Crystals d'énergie expliqués et ce qu'ils font (cristal jaune)

2 Il y a quelques semaines By 尊渡假赌尊渡假赌尊渡假赌

Repo: Comment relancer ses coéquipiers

1 Il y a quelques mois By 尊渡假赌尊渡假赌尊渡假赌

Hello Kitty Island Adventure: Comment obtenir des graines géantes

4 Il y a quelques semaines By 尊渡假赌尊渡假赌尊渡假赌

Combien de temps faut-il pour battre Split Fiction?

3 Il y a quelques semaines By DDD

R.E.P.O. Enregistrer l'emplacement du fichier: où est-il et comment le protéger?

3 Il y a quelques semaines By DDD

Afficher plus

Outils chauds

Bloc-notes++7.3.1

Éditeur de code facile à utiliser et gratuit

SublimeText3 version chinoise

Version chinoise, très simple à utiliser

Envoyer Studio 13.0.1

Puissant environnement de développement intégré PHP

Dreamweaver CS6

Outils de développement Web visuel

SublimeText3 version Mac

Logiciel d'édition de code au niveau de Dieu (SublimeText3)

Afficher plus

Sujets chauds

Où se trouve l'entrée de connexion pour la messagerie Gmail ?

7338

Tutoriel Java

1627

Tutoriel CakePHP

1352

Tutoriel Laravel

1265

Tutoriel PHP

1210

Afficher plus

Related knowledge

Comment résoudre le problème selon lequel l'objet nommé existe déjà dans la base de données sqlserver Apr 05, 2024 pm 09:42 PM

Pour les objets du même nom qui existent déjà dans la base de données SQL Server, les étapes suivantes doivent être suivies : Confirmez le type d'objet (table, vue, procédure stockée). IF NOT EXISTS peut être utilisé pour ignorer la création si l'objet est vide. Si l'objet contient des données, utilisez un nom différent ou modifiez la structure. Utilisez DROP pour supprimer des objets existants (soyez prudent, sauvegarde recommandée). Vérifiez les modifications de schéma pour vous assurer qu'il n'y a aucune référence à des objets supprimés ou renommés.

Comment importer un fichier mdf dans sqlserver Apr 08, 2024 am 11:41 AM

Les étapes d'importation sont les suivantes : Copiez le fichier MDF dans le répertoire de données de SQL Server (généralement C:\Program Files\Microsoft SQL Server\MSSQL\DATA). Dans SQL Server Management Studio (SSMS), ouvrez la base de données et sélectionnez Attacher. Cliquez sur le bouton Ajouter et sélectionnez le fichier MDF. Confirmez le nom de la base de données et cliquez sur le bouton OK.

Que faire si le service sqlserver ne peut pas être démarré Apr 05, 2024 pm 10:00 PM

Lorsque le service SQL Server ne parvient pas à démarrer, voici quelques étapes à résoudre : Consultez le journal des erreurs pour déterminer la cause première. Assurez-vous que le compte de service est autorisé à démarrer le service. Vérifiez si les services de dépendance sont en cours d'exécution. Désactivez le logiciel antivirus. Réparez l'installation de SQL Server. Si la réparation ne fonctionne pas, réinstallez SQL Server.

Comment vérifier le numéro de port sqlserver Apr 05, 2024 pm 09:57 PM

Pour afficher le numéro de port SQL Server : Ouvrez SSMS et connectez-vous au serveur. Recherchez le nom du serveur dans l'Explorateur d'objets, cliquez dessus avec le bouton droit et sélectionnez Propriétés. Dans l'onglet Connexion, affichez le champ Port TCP.

Comment récupérer une base de données supprimée accidentellement dans SQLserver Apr 05, 2024 pm 10:39 PM

Si vous supprimez accidentellement une base de données SQL Server, vous pouvez suivre les étapes suivantes pour la récupérer : arrêter l'activité de la base de données ; sauvegarder les fichiers journaux ; vérifier les options de récupération : restaurer à partir d'une sauvegarde ; utiliser DBCC CHECKDB ; outils de fête. Veuillez sauvegarder régulièrement votre base de données et activer la journalisation des transactions pour éviter toute perte de données.

Où est la base de données sqlserver ? Apr 05, 2024 pm 08:21 PM

Les fichiers de base de données SQL Server sont généralement stockés à l'emplacement par défaut suivant : Windows : C:\Program Files\Microsoft SQL Server\MSSQL\DATALinux : /var/opt/mssql/data L'emplacement du fichier de base de données peut être personnalisé en modifiant le chemin du fichier de base de données. paramètre.

Comment résoudre l'erreur de connexion Java SqlServer May 01, 2023 am 09:22 AM

Le problème a été constaté que cette fois j'utilisais la base de données SqlServer, que je n'avais pas utilisée auparavant, mais le problème n'était pas grave. Après avoir connecté SqlServer selon les étapes du document sur les exigences, j'ai démarré le projet SpringBoot et trouvé un. erreur, comme suit : Au début, je pensais qu'il s'agissait d'une connexion SqlServer, alors je suis allé vérifier la base de données et j'ai constaté que tout dans la base de données était normal. J'ai d'abord demandé à mes collègues s'ils avaient un tel problème, et. J'ai découvert que ce n'était pas le cas, alors j'ai commencé ma meilleure partie en faisant face à la programmation Baidu. Le message d'erreur spécifique que j'ai commencé à résoudre était le suivant, j'ai donc lancé le rapport d'erreurs Baidu : ERRORc.a.d.p.DruidDataSource$CreateCo

Comment supprimer sqlserver si l'installation échoue ? Apr 05, 2024 pm 11:27 PM

Si l'installation de SQL Server échoue, vous pouvez la nettoyer en suivant ces étapes : Désinstaller SQL Server Supprimer les clés de registre Supprimer les fichiers et dossiers Redémarrer l'ordinateur

See all articles