SQLSERVER用无中生有的思想来替代游标
- WBOYオリジナル
- 2016-06-07 15:21:03985ブラウズ
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
图1
LZ说原表就是类似上面那样，实际表中pay_lv_会有很多列至少100列，我这里为了测试只建了25个pay_lv_列
而LZ希望select出来的结果是下图那样
图2
client列和pay_level列不变，增加一个pay_cost列
pay_cost列根据pay_level列的值去取pay_lv_列的值，或者我用下面的图片会更加明白
图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列
<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> #tView 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;
图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>
图5
上面两个方法：CASE WHEN和UNPIVOT函数可以用拼接SQL的方法来做，不过由于本人功力不够，写不出来
（3）游标
我不喜欢使用游标，主要有两个原因
1、每次用的时候，要打开笔记本看语法
2、占用资源
我使用了下面的sql语句来解决LZ的问题
<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
重点是怎麽模仿游标
其实这个方法是最原始的方法，之前解决论坛问题的时候用过，想不到这次也能用上
<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 までご連絡ください。