ホームページ

データベース

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

Java チュートリアル

1625

CakePHP チュートリアル

1349

Laravel チュートリアル

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) にコピーします。 SQL Server Management Studio (SSMS) でデータベースを開き、[アタッチ] を選択します。「追加」ボタンをクリックして、MDF ファイルを選択します。データベース名を確認し、「OK」ボタンをクリックします。

sqlserver サービスを開始できない場合の対処方法 Apr 05, 2024 pm 10:00 PM

SQL Server サービスの開始に失敗した場合の解決手順は次のとおりです。エラーログを確認して、根本原因を特定します。サービスアカウントにサービスを開始する権限があることを確認してください。依存関係サービスが実行されているかどうかを確認します。ウイルス対策ソフトウェアを無効にします。 SQL Server のインストールを修復します。修復が機能しない場合は、SQL Server を再インストールします。

SQLサーバーのポート番号を確認する方法 Apr 05, 2024 pm 09:57 PM

SQL Server のポート番号を表示するには: SSMS を開いてサーバーに接続します。オブジェクトエクスプローラーでサーバー名を見つけ、右クリックして [プロパティ] を選択します。「接続」タブで、「TCP ポート」フィールドを表示します。

sqlserverで誤って削除したデータベースを回復する方法 Apr 05, 2024 pm 10:39 PM

SQL Server データベースを誤って削除した場合は、次の手順を実行して回復できます: データベースアクティビティの停止、ログファイルのバックアップ、データベースログの確認、回復オプション: バックアップからの復元、トランザクションログからの復元、DBCC CHECKDB の使用、3 番目の使用パーティーツール。データ損失を防ぐために、データベースを定期的にバックアップし、トランザクションログを有効にしてください。

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版

ホットトピック