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中的慢速查詢？ （慢查詢日誌，performance_schema）Apr 10, 2025 am 09:36 AM要優化MySQL慢查詢，需使用slowquerylog和performance_schema：1.啟用slowquerylog並設置閾值，記錄慢查詢；2.利用performance_schema分析查詢執行細節，找出性能瓶頸並優化。
MySQL和SQL：開發人員的基本技能Apr 10, 2025 am 09:30 AMMySQL和SQL是開發者必備技能。 1.MySQL是開源的關係型數據庫管理系統，SQL是用於管理和操作數據庫的標準語言。 2.MySQL通過高效的數據存儲和檢索功能支持多種存儲引擎，SQL通過簡單語句完成複雜數據操作。 3.使用示例包括基本查詢和高級查詢，如按條件過濾和排序。 4.常見錯誤包括語法錯誤和性能問題，可通過檢查SQL語句和使用EXPLAIN命令優化。 5.性能優化技巧包括使用索引、避免全表掃描、優化JOIN操作和提升代碼可讀性。
描述MySQL異步主奴隸複製過程。Apr 10, 2025 am 09:30 AMMySQL異步主從復制通過binlog實現數據同步，提升讀性能和高可用性。 1)主服務器記錄變更到binlog；2)從服務器通過I/O線程讀取binlog；3)從服務器的SQL線程應用binlog同步數據。
mysql：簡單的概念，用於輕鬆學習Apr 10, 2025 am 09:29 AMMySQL是一個開源的關係型數據庫管理系統。 1）創建數據庫和表：使用CREATEDATABASE和CREATETABLE命令。 2）基本操作：INSERT、UPDATE、DELETE和SELECT。 3）高級操作：JOIN、子查詢和事務處理。 4）調試技巧：檢查語法、數據類型和權限。 5）優化建議：使用索引、避免SELECT*和使用事務。
MySQL：數據庫的用戶友好介紹Apr 10, 2025 am 09:27 AMMySQL的安裝和基本操作包括：1.下載並安裝MySQL，設置根用戶密碼；2.使用SQL命令創建數據庫和表，如CREATEDATABASE和CREATETABLE；3.執行CRUD操作，使用INSERT,SELECT,UPDATE,DELETE命令；4.創建索引和存儲過程以優化性能和實現複雜邏輯。通過這些步驟，你可以從零開始構建和管理MySQL數據庫。
InnoDB緩衝池如何工作，為什麼對性能至關重要？Apr 09, 2025 am 12:12 AMInnoDBBufferPool通過將數據和索引頁加載到內存中來提升MySQL數據庫的性能。 1)數據頁加載到BufferPool中，減少磁盤I/O。 2)臟頁被標記並定期刷新到磁盤。 3)LRU算法管理數據頁淘汰。 4)預讀機制提前加載可能需要的數據頁。
MySQL：初學者的數據管理易用性Apr 09, 2025 am 12:07 AMMySQL適合初學者使用，因為它安裝簡單、功能強大且易於管理數據。 1.安裝和配置簡單，適用於多種操作系統。 2.支持基本操作如創建數據庫和表、插入、查詢、更新和刪除數據。 3.提供高級功能如JOIN操作和子查詢。 4.可以通過索引、查詢優化和分錶分區來提升性能。 5.支持備份、恢復和安全措施，確保數據的安全和一致性。
與MySQL中使用索引相比，全表掃描何時可以更快？Apr 09, 2025 am 12:05 AM全表掃描在MySQL中可能比使用索引更快，具體情況包括：1)數據量較小時；2)查詢返回大量數據時；3)索引列不具備高選擇性時；4)複雜查詢時。通過分析查詢計劃、優化索引、避免過度索引和定期維護表，可以在實際應用中做出最優選擇。
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
AI Hentai Generator
免費產生 AI 無盡。
熱門文章
R.E.P.O.能量晶體解釋及其做什麼（黃色晶體）
3 週前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.最佳圖形設置
3 週前By尊渡假赌尊渡假赌尊渡假赌
刺客信條陰影：貝殼謎語解決方案
1 週前ByDDD
R.E.P.O.如果您聽不到任何人，如何修復音頻
3 週前By尊渡假赌尊渡假赌尊渡假赌
WWE 2K25：如何解鎖Myrise中的所有內容
3 週前By尊渡假赌尊渡假赌尊渡假赌
熱工具
mPDF
mPDF是一個PHP庫，可以從UTF-8編碼的HTML產生PDF檔案。原作者Ian Back編寫mPDF以從他的網站上「即時」輸出PDF文件，並處理不同的語言。與原始腳本如HTML2FPDF相比，它的速度較慢，並且在使用Unicode字體時產生的檔案較大，但支援CSS樣式等，並進行了大量增強。支援幾乎所有語言，包括RTL（阿拉伯語和希伯來語）和CJK（中日韓）。支援嵌套的區塊級元素（如P、DIV），
SublimeText3 Linux新版
SublimeText3 Linux最新版
MantisBT
Mantis是一個易於部署的基於Web的缺陷追蹤工具，用於幫助產品缺陷追蹤。它需要PHP、MySQL和一個Web伺服器。請查看我們的演示和託管服務。
SublimeText3漢化版
中文版，非常好用
Safe Exam Browser
Safe Exam Browser是一個安全的瀏覽器環境，安全地進行線上考試。該軟體將任何電腦變成一個安全的工作站。它控制對任何實用工具的訪問，並防止學生使用未經授權的資源。
