一句SQL实现MYSQL的递归查询

插入测试数据

<span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> `treenodes` (`id`, `nodename`, `pid`) <span style="color: #0000ff;">VALUES</span><span style="color: #000000;">
(</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">1</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">A</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">0</span><span style="color: #ff0000;">‘</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">B</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">1</span><span style="color: #ff0000;">‘</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">3</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">C</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">1</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">),
(</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">4</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">D</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2</span><span style="color: #ff0000;">‘</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">5</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">E</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2</span><span style="color: #ff0000;">‘</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">6</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">F</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">3</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">),
(</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">7</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">G</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">6</span><span style="color: #ff0000;">‘</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">8</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">H</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">0</span><span style="color: #ff0000;">‘</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">9</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">I</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">8</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">),
(</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">10</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">J</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">8</span><span style="color: #ff0000;">‘</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">11</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">K</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">8</span><span style="color: #ff0000;">‘</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">12</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">L</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">9</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">),
(</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">13</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">M</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">9</span><span style="color: #ff0000;">‘</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">14</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">N</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">12</span><span style="color: #ff0000;">‘</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">15</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">O</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">12</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">),
(</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">16</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">15</span><span style="color: #ff0000;">‘</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">17</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Q</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">15</span><span style="color: #ff0000;">‘</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">18</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">R</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">3</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">),
(</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">19</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">S</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2</span><span style="color: #ff0000;">‘</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">20</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">T</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">6</span><span style="color: #ff0000;">‘</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">21</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">U</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">8</span><span style="color: #ff0000;">‘</span>);

查询语句

 <span style="color: #0000ff;">SELECT</span> id <span style="color: #0000ff;">AS</span> ID,pid <span style="color: #0000ff;">AS</span> 父ID ,levels <span style="color: #0000ff;">AS</span> 父到子之间级数, paths <span style="color: #0000ff;">AS</span> 父到子路径 <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> (
     </span><span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> id,pid,
     </span><span style="color: #008000;">@le</span>:<span style="color: #808080;">=</span> <span style="color: #0000ff;">IF</span> (pid <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">0</span> ,<span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">,  
         </span><span style="color: #0000ff;">IF</span>( LOCATE( CONCAT(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">|</span><span style="color: #ff0000;">‘</span>,pid,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">:</span><span style="color: #ff0000;">‘</span>),<span style="color: #008000;">@pathlevel</span>)   <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">  ,      
                  SUBSTRING_INDEX( SUBSTRING_INDEX(</span><span style="color: #008000;">@pathlevel</span>,CONCAT(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">|</span><span style="color: #ff0000;">‘</span>,pid,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">:</span><span style="color: #ff0000;">‘</span>),<span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">1</span>),<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">|</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">1</span>) <span style="color: #808080;">+</span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">
        ,</span><span style="color: #008000;">@le</span><span style="color: #808080;">+</span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">) ) levels
     , </span><span style="color: #008000;">@pathlevel</span>:<span style="color: #808080;">=</span> CONCAT(<span style="color: #008000;">@pathlevel</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">|</span><span style="color: #ff0000;">‘</span>,id,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">:</span><span style="color: #ff0000;">‘</span>, <span style="color: #008000;">@le</span> ,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">|</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">) pathlevel
      , </span><span style="color: #008000;">@pathnodes</span>:<span style="color: #808080;">=</span> <span style="color: #0000ff;">IF</span>( pid <span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">0</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,0</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">, 
           CONCAT_WS(</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
           </span><span style="color: #0000ff;">IF</span>( LOCATE( CONCAT(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">|</span><span style="color: #ff0000;">‘</span>,pid,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">:</span><span style="color: #ff0000;">‘</span>),<span style="color: #008000;">@pathall</span>) <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">  , 
               SUBSTRING_INDEX( SUBSTRING_INDEX(</span><span style="color: #008000;">@pathall</span>,CONCAT(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">|</span><span style="color: #ff0000;">‘</span>,pid,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">:</span><span style="color: #ff0000;">‘</span>),<span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">1</span>),<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">|</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">)
              ,</span><span style="color: #008000;">@pathnodes</span><span style="color: #000000;"> ) ,pid  ) )paths
    ,</span><span style="color: #008000;">@pathall</span>:<span style="color: #808080;">=</span>CONCAT(<span style="color: #008000;">@pathall</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">|</span><span style="color: #ff0000;">‘</span>,id,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">:</span><span style="color: #ff0000;">‘</span>, <span style="color: #008000;">@pathnodes</span> ,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">|</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">) pathall 
        </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">  treenodes, 
    (</span><span style="color: #0000ff;">SELECT</span> <span style="color: #008000;">@le</span>:<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">0</span>,<span style="color: #008000;">@pathlevel</span>:<span style="color: #808080;">=</span><span style="color: #ff0000;">‘‘</span>, <span style="color: #008000;">@pathall</span>:<span style="color: #808080;">=</span><span style="color: #ff0000;">‘‘</span>,<span style="color: #008000;">@pathnodes</span>:<span style="color: #808080;">=</span><span style="color: #ff0000;">‘‘</span><span style="color: #000000;">) vv
    </span><span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span><span style="color: #000000;">  pid,id
    ) src
</span><span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> id

最后的结果如下：

    ID   父ID  父到子之间级数  父到子路径
------  ------  ------------  ---------------
     1       0        0           ,0             
     2       1        1           ,0,1           
     3       1        1           ,0,1           
     4       2        2           ,0,1,2         
     5       2        2           ,0,1,2         
     6       3        2           ,0,1,3         
     7       6        3           ,0,1,3,6       
     8       0        0           ,0             
     9       8        1           ,0,8           
    10       8        1           ,0,8           
    11       8        1           ,0,8           
    12       9        2           ,0,8,9         
    13       9        2           ,0,8,9         
    14      12        3           ,0,8,9,12      
    15      12        3           ,0,8,9,12      
    16      15        4           ,0,8,9,12,15   
    17      15        4           ,0,8,9,12,15   
    18       3        2           ,0,1,3         
    19       2        2           ,0,1,2         
    20       6        3           ,0,1,3,6       
    21       8        1           ,0,8       

一句SQL实现MYSQL的递归查询

标签：