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
mysql：blob和其他无-SQL存储，有什么区别？May 13, 2025 am 12:14 AMmysql'sblobissuitableForStoringBinaryDataWithInareLationalDatabase，而alenosqloptionslikemongodb，redis和calablesolutionsoluntionsoluntionsoluntionsolundortionsolunsolunsstructureddata.blobobobsimplobissimplobisslowderperformandperformanceperformancewithlararengelitiate;
mySQL添加用户：语法，选项和安全性最佳实践May 13, 2025 am 12:12 AMtoaddauserinmysql，使用：createUser'username'@'host'Indessify'password'; there'showtodoitsecurely：1）choosethehostcarecarefullytocon trolaccess.2）setResourcelimitswithoptionslikemax_queries_per_hour.3）usestrong，iniquepasswords.4）Enforcessl/tlsconnectionswith
MySQL：如何避免字符串数据类型常见错误？May 13, 2025 am 12:09 AMtoAvoidCommonMistakeswithStringDatatatPesInMysQl，CloseStringTypenuances，chosethirtightType，andManageEngencodingAndCollationsEttingsefectery.1）usecharforfixed lengengters lengengtings，varchar forbariaible lengength，varchariable length，andtext/blobforlabforlargerdata.2 seterters seterters seterters seterters
mySQL：字符串数据类型和枚举？May 13, 2025 am 12:05 AMmysqloffersechar，varchar，text，and denumforstringdata.usecharforfixed Lengttrings，varcharerforvariable长度，文本forlarger文本，andenumforenforcingDataAntegrityWithaEtofValues。
mysql blob：如何优化斑点请求May 13, 2025 am 12:03 AM优化MySQLBLOB请求可以通过以下策略：1.减少BLOB查询频率，使用独立请求或延迟加载；2.选择合适的BLOB类型（如TINYBLOB）；3.将BLOB数据分离到单独表中；4.在应用层压缩BLOB数据；5.对BLOB元数据建立索引。这些方法结合实际应用中的监控、缓存和数据分片，可以有效提升性能。
将用户添加到MySQL：完整的教程May 12, 2025 am 12:14 AM掌握添加MySQL用户的方法对于数据库管理员和开发者至关重要，因为它确保数据库的安全性和访问控制。1)使用CREATEUSER命令创建新用户，2)通过GRANT命令分配权限，3)使用FLUSHPRIVILEGES确保权限生效，4)定期审计和清理用户账户以维护性能和安全。
掌握mySQL字符串数据类型：varchar vs.文本与charMay 12, 2025 am 12:12 AMchosecharforfixed-lengthdata，varcharforvariable-lengthdata，andtextforlargetextfield.1）chariseffity forconsistent-lengthdatalikecodes.2）varcharsuitsvariable-lengthdatalikenames，ballancingflexibilitibility andperformance.3）
MySQL：字符串数据类型和索引：最佳实践May 12, 2025 am 12:11 AM在MySQL中处理字符串数据类型和索引的最佳实践包括：1)选择合适的字符串类型，如CHAR用于固定长度，VARCHAR用于可变长度，TEXT用于大文本；2)谨慎索引，避免过度索引，针对常用查询创建索引；3)使用前缀索引和全文索引优化长字符串搜索；4)定期监控和优化索引，保持索引小巧高效。通过这些方法，可以在读取和写入性能之间取得平衡，提升数据库效率。
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
Video Face Swap
使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸！
热门文章
<🎜>：种植花园 - 完整的突变指南
3 周前ByDDD
<🎜>：泡泡胶模拟器无穷大 - 如何获取和使用皇家钥匙
3 周前By尊渡假赌尊渡假赌尊渡假赌
如何修复KB5055612无法在Windows 10中安装？
3 周前ByDDD
北端：融合系统，解释
3 周前By尊渡假赌尊渡假赌尊渡假赌
Mandragora：巫婆树的耳语 - 如何解锁抓钩
3 周前By尊渡假赌尊渡假赌尊渡假赌
热工具
SecLists
SecLists是最终安全测试人员的伙伴。它是一个包含各种类型列表的集合，这些列表在安全评估过程中经常使用，都在一个地方。SecLists通过方便地提供安全测试人员可能需要的所有列表，帮助提高安全测试的效率和生产力。列表类型包括用户名、密码、URL、模糊测试有效载荷、敏感数据模式、Web shell等等。测试人员只需将此存储库拉到新的测试机上，他就可以访问到所需的每种类型的列表。
DVWA
Damn Vulnerable Web App (DVWA) 是一个PHP/MySQL的Web应用程序，非常容易受到攻击。它的主要目标是成为安全专业人员在合法环境中测试自己的技能和工具的辅助工具，帮助Web开发人员更好地理解保护Web应用程序的过程，并帮助教师/学生在课堂环境中教授/学习Web应用程序安全。DVWA的目标是通过简单直接的界面练习一些最常见的Web漏洞，难度各不相同。请注意，该软件中
Dreamweaver CS6
视觉化网页开发工具
Atom编辑器mac版下载
最流行的的开源编辑器
Dreamweaver Mac版
视觉化网页开发工具
