当前位置：Gxlcms > 数据库问题 > 【Sql Server】SQL SERVER 递归查询

【Sql Server】SQL SERVER 递归查询

时间：2021-07-01 10:21:17 帮助过：3人阅读

　　CTE：公用表达式Common Table Expression 是SQL SERVER 2005版本之后引入的一个特性；

#填充测试数据

1、sql

  1. <span style="color: #008080;"> 1</span> <span style="color: #0000ff;">Create</span> <span style="color: #0000ff;">table</span> GroupInfo1(<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Id</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">int</span>,<span style="color: #ff0000;">[</span><span style="color: #ff0000;">GroupName</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">nvarchar</span>(<span style="color: #800000; font-weight: bold;">50</span>),<span style="color: #ff0000;">[</span><span style="color: #ff0000;">ParentGroupId</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">int</span><span style="color: #000000;">)
  2. </span><span style="color: #008080;"> 2</span> 
  3. <span style="color: #008080;"> 3</span> <span style="color: #0000ff;">Insert</span><span style="color: #000000;"> GroupInfo1
  4. </span><span style="color: #008080;"> 4</span> 
  5. <span style="color: #008080;"> 5</span> <span style="color: #0000ff;">select</span> <span style="color: #800000; font-weight: bold;">0</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">某某大学</span><span style="color: #ff0000;">‘</span>,<span style="color: #0000ff;">null</span> <span style="color: #0000ff;">union</span> <span style="color: #808080;">all</span>
  6. <span style="color: #008080;"> 6</span> 
  7. <span style="color: #008080;"> 7</span> <span style="color: #0000ff;">select</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;">0</span> <span style="color: #0000ff;">union</span> <span style="color: #808080;">all</span>
  8. <span style="color: #008080;"> 8</span> <span style="color: #0000ff;">select</span> <span style="color: #800000; font-weight: bold;">2</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: #0000ff;">union</span> <span style="color: #808080;">all</span>
  9. <span style="color: #008080;"> 9</span> <span style="color: #0000ff;">select</span> <span style="color: #800000; font-weight: bold;">3</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: #0000ff;">union</span> <span style="color: #808080;">all</span>
  10. <span style="color: #008080;">10</span> <span style="color: #0000ff;">select</span> <span style="color: #800000; font-weight: bold;">4</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">英语专业一班</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">2</span> <span style="color: #0000ff;">union</span> <span style="color: #808080;">all</span>
  11. <span style="color: #008080;">11</span> <span style="color: #0000ff;">select</span> <span style="color: #800000; font-weight: bold;">5</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">英语专业二班</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">2</span> <span style="color: #0000ff;">union</span> <span style="color: #808080;">all</span>
  12. <span style="color: #008080;">12</span> <span style="color: #0000ff;">select</span> <span style="color: #800000; font-weight: bold;">6</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">日语专业一班</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">3</span> <span style="color: #0000ff;">union</span> <span style="color: #808080;">all</span>
  13. <span style="color: #008080;">13</span> <span style="color: #0000ff;">select</span> <span style="color: #800000; font-weight: bold;">7</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">日语专业二班</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">3</span> <span style="color: #0000ff;">union</span> <span style="color: #808080;">all</span>
  14. <span style="color: #008080;">14</span> 
  15. <span style="color: #008080;">15</span> <span style="color: #0000ff;">select</span> <span style="color: #800000; font-weight: bold;">8</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">法学院</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">union</span> <span style="color: #808080;">all</span>
  16. <span style="color: #008080;">16</span> <span style="color: #0000ff;">select</span> <span style="color: #800000; font-weight: bold;">9</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">刑法学专业</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">8</span> <span style="color: #0000ff;">union</span> <span style="color: #808080;">all</span>
  17. <span style="color: #008080;">17</span> <span style="color: #0000ff;">select</span> <span style="color: #800000; font-weight: bold;">10</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">经济法学专业</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">8</span> <span style="color: #0000ff;">union</span> <span style="color: #808080;">all</span>
  18. <span style="color: #008080;">18</span> <span style="color: #0000ff;">select</span> <span style="color: #800000; font-weight: bold;">11</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">刑法学专业一班</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">9</span> <span style="color: #0000ff;">union</span> <span style="color: #808080;">all</span>
  19. <span style="color: #008080;">19</span> <span style="color: #0000ff;">select</span> <span style="color: #800000; font-weight: bold;">12</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">刑法学专业二班</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">9</span> <span style="color: #0000ff;">union</span> <span style="color: #808080;">all</span>
  20. <span style="color: #008080;">20</span> <span style="color: #0000ff;">select</span> <span style="color: #800000; font-weight: bold;">13</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">经济法学专业一班</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">10</span> <span style="color: #0000ff;">union</span> <span style="color: #808080;">all</span>
  21. <span style="color: #008080;">21</span> <span style="color: #0000ff;">select</span> <span style="color: #800000; font-weight: bold;">14</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">经济法学专业二班</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">10</span>

2、效果图

 技术分享图片

#递归实现Demo

1、根据指定的节点向上获取所有父节点,向下获取所有子节点

  1. <span style="color: #008080;"> 1</span> <span style="color: #008080;">--</span><span style="color: #008080;">根据指定的节点向下获取所有子节点</span>
  2. <span style="color: #008080;"> 2</span> <span style="color: #0000ff;">with</span>
  3. <span style="color: #008080;"> 3</span> <span style="color: #000000;">CTE
  4. </span><span style="color: #008080;"> 4</span> <span style="color: #0000ff;">as</span>
  5. <span style="color: #008080;"> 5</span> <span style="color: #000000;">(
  6. </span><span style="color: #008080;"> 6</span>     <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> GroupInfo <span style="color: #0000ff;">where</span> Id<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">1</span>
  7. <span style="color: #008080;"> 7</span>     <span style="color: #0000ff;">union</span> <span style="color: #808080;">all</span>
  8. <span style="color: #008080;"> 8</span>     <span style="color: #0000ff;">select</span> G.<span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> CTE <span style="color: #0000ff;">inner</span> <span style="color: #808080;">join</span> GroupInfo <span style="color: #0000ff;">as</span><span style="color: #000000;"> G
  9. </span><span style="color: #008080;"> 9</span>     <span style="color: #0000ff;">on</span> CTE.Id<span style="color: #808080;">=</span><span style="color: #000000;">G.ParentGroupId
  10. </span><span style="color: #008080;">10</span> <span style="color: #000000;">)
  11. </span><span style="color: #008080;">11</span> <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> CTE <span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span> Id

 

  1. <span style="color: #008080;"> 1</span> <span style="color: #008080;">--</span><span style="color: #008080;">根据指定的节点向上获取所有父节点</span>
  2. <span style="color: #008080;"> 2</span> <span style="color: #0000ff;">with</span>
  3. <span style="color: #008080;"> 3</span> <span style="color: #000000;">CTE
  4. </span><span style="color: #008080;"> 4</span> <span style="color: #0000ff;">as</span>
  5. <span style="color: #008080;"> 5</span> <span style="color: #000000;">(
  6. </span><span style="color: #008080;"> 6</span>     <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> GroupInfo <span style="color: #0000ff;">where</span> Id<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">14</span>
  7. <span style="color: #008080;"> 7</span>     <span style="color: #0000ff;">union</span> <span style="color: #808080;">all</span>
  8. <span style="color: #008080;"> 8</span>     <span style="color: #0000ff;">select</span> G.<span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> CTE <span style="color: #0000ff;">inner</span> <span style="color: #808080;">join</span> GroupInfo <span style="color: #0000ff;">as</span><span style="color: #000000;"> G
  9. </span><span style="color: #008080;"> 9</span>     <span style="color: #0000ff;">on</span> CTE.ParentGroupId<span style="color: #808080;">=</span><span style="color: #000000;">G.Id
  10. </span><span style="color: #008080;">10</span> <span style="color: #000000;">)
  11. </span><span style="color: #008080;">11</span> <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> CTE <span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span> Id

2、构造递归路径

  1. <span style="color: #008080;"> 1</span> <span style="color: #008080;">--</span><span style="color: #008080;">构造递归路径</span>
  2. <span style="color: #008080;"> 2</span> <span style="color: #0000ff;">with</span>
  3. <span style="color: #008080;"> 3</span> <span style="color: #000000;">CTE
  4. </span><span style="color: #008080;"> 4</span> <span style="color: #0000ff;">as</span>
  5. <span style="color: #008080;"> 5</span> <span style="color: #000000;">(
  6. </span><span style="color: #008080;"> 6</span>     <span style="color: #0000ff;">select</span> Id,GroupName,ParentGroupId,GroupPath<span style="color: #808080;">=</span><span style="color: #ff00ff;">CAST</span>( GroupName <span style="color: #0000ff;">as</span> <span style="color: #0000ff;">nvarchar</span>(<span style="color: #ff00ff;">max</span>)) <span style="color: #0000ff;">from</span> GroupInfo <span style="color: #0000ff;">where</span> Id<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">1</span>
  7. <span style="color: #008080;"> 7</span>     <span style="color: #0000ff;">union</span> <span style="color: #808080;">all</span>
  8. <span style="color: #008080;"> 8</span>     <span style="color: #0000ff;">select</span> G.<span style="color: #808080;">*</span>,<span style="color: #ff00ff;">CAST</span>(CTE.GroupPath<span style="color: #808080;">+</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">//</span><span style="color: #ff0000;">‘</span><span style="color: #808080;">+</span>G.GroupName <span style="color: #0000ff;">as</span> <span style="color: #0000ff;">nvarchar</span>(<span style="color: #ff00ff;">max</span>)) <span style="color: #0000ff;">as</span> GroupPath <span style="color: #0000ff;">from</span><span style="color: #000000;"> CTE 
  9. </span><span style="color: #008080;"> 9</span>     <span style="color: #0000ff;">inner</span> <span style="color: #808080;">join</span> GroupInfo <span style="color: #0000ff;">as</span><span style="color: #000000;"> G
  10. </span><span style="color: #008080;">10</span>     <span style="color: #0000ff;">on</span> CTE.Id<span style="color: #808080;">=</span><span style="color: #000000;">G.ParentGroupId
  11. </span><span style="color: #008080;">11</span> <span style="color: #000000;">)
  12. </span><span style="color: #008080;">12</span> <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> CTE

技术分享图片

 3、分组递归，将同一条分支上节点放到一起

  1. <span style="color: #008080;"> 1</span> <span style="color: #008080;">--</span><span style="color: #008080;">通过id字段的字符串的拼接，形成sort字段，再通过sort排序，来实现同一分支上的节点放到一起</span>
  2. <span style="color: #008080;"> 2</span> <span style="color: #0000ff;">WITH</span>    
  3. <span style="color: #008080;"> 3</span> <span style="color: #000000;">CTE
  4. </span><span style="color: #008080;"> 4</span> <span style="color: #0000ff;">AS</span> 
  5. <span style="color: #008080;"> 5</span> <span style="color: #000000;">( 
  6. </span><span style="color: #008080;"> 6</span>     <span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> ,<span style="color: #ff00ff;">CAST</span>(<span style="color: #808080;">RIGHT</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">000</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> <span style="color: #ff00ff;">CAST</span>(<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Id</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">AS</span> <span style="color: #0000ff;">VARCHAR</span>), <span style="color: #800000; font-weight: bold;">3</span>) <span style="color: #0000ff;">AS</span> <span style="color: #0000ff;">VARCHAR</span>(<span style="color: #ff00ff;">MAX</span>)) <span style="color: #0000ff;">AS</span> sort <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> GroupInfo
  7. </span><span style="color: #008080;"> 7</span>     <span style="color: #0000ff;">WHERE</span> ParentGroupId <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">0</span>
  8. <span style="color: #008080;"> 8</span>     <span style="color: #0000ff;">UNION</span> <span style="color: #808080;">ALL</span>
  9. <span style="color: #008080;"> 9</span>     <span style="color: #0000ff;">SELECT</span>   GroupInfo.<span style="color: #808080;">*</span> ,<span style="color: #ff00ff;">CAST</span>(sort <span style="color: #808080;">+</span> <span style="color: #808080;">RIGHT</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">000</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> <span style="color: #ff00ff;">CAST</span>(GroupInfo.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Id</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">AS</span> <span style="color: #0000ff;">VARCHAR</span>),<span style="color: #800000; font-weight: bold;">3</span>) <span style="color: #0000ff;">AS</span> <span style="color: #0000ff;">VARCHAR</span>(<span style="color: #ff00ff;">MAX</span>)) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> sort
  10. </span><span style="color: #008080;">10</span>     <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> CTE
  11. </span><span style="color: #008080;">11</span>     <span style="color: #0000ff;">INNER</span> <span style="color: #808080;">JOIN</span> GroupInfo <span style="color: #0000ff;">ON</span> CTE.Id <span style="color: #808080;">=</span><span style="color: #000000;"> GroupInfo.ParentGroupId
  12. </span><span style="color: #008080;">12</span> <span style="color: #000000;">)
  13. </span><span style="color: #008080;">13</span> <span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span> CTE <span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> sort

 技术分享图片

4、递归层级查询（查询出节点所属的层级）

  1. <span style="color: #008080;">1</span> <span style="color: #008080;">--</span><span style="color: #008080;">查询节点层级</span>
  2. <span style="color: #008080;">2</span> <span style="color: #0000ff;">WITH</span> CTE <span style="color: #0000ff;">AS</span><span style="color: #000000;"> (
  3. </span><span style="color: #008080;">3</span>     <span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span>,<span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">AS</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">Level</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">FROM</span> GroupInfo <span style="color: #0000ff;">WHERE</span> ParentGroupId<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">0</span>
  4. <span style="color: #008080;">4</span>     <span style="color: #0000ff;">UNION</span> <span style="color: #808080;">ALL</span>
  5. <span style="color: #008080;">5</span>     <span style="color: #0000ff;">SELECT</span> G.<span style="color: #808080;">*</span>,CTE.<span style="color: #0000ff;">Level</span><span style="color: #808080;">+</span><span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">FROM</span> GroupInfo <span style="color: #0000ff;">as</span><span style="color: #000000;"> G 
  6. </span><span style="color: #008080;">6</span>     <span style="color: #808080;">JOIN</span> CTE <span style="color: #0000ff;">ON</span> CTE.Id <span style="color: #808080;">=</span><span style="color: #000000;">G.ParentGroupId
  7. </span><span style="color: #008080;">7</span> <span style="color: #000000;">)
  8. </span><span style="color: #008080;">8</span> <span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span> CTE

 技术分享图片

 

【Sql Server】SQL SERVER 递归查询

标签：src   alt   inner   weight   info   res   evel   cas   har   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！