with 关键字实现递归查询

 

通常用来实现树形结构

首先来看一下表数据

 

 以下代码是递归查询北京市的树结构

WITH org AS (
 SELECT Code, name, parentCode, 1 AS level
 FROM TestCity 
WHERE code = '01'
 UNION ALL
 SELECT t1.Code, t1.name, t1.parentCode, t2.level + 1 AS level
 FROM TestCity t1 INNER JOIN org t2
   ON t1.parentCode = t2.Code
)
SELECT
*
FROM org

　　

 

注意：如果要在with表达式中计算，需要保证两边的type一致，如

 

WITH org AS (
 SELECT Code, name, parentCode, 1 AS level, tname = CAST('' as nvarchar(20))
 FROM TestCity 
WHERE code = '01'
 UNION ALL
 SELECT t1.Code, t1.name, t1.parentCode, t2.level + 1 AS level, tanme = CAST(N'|--' + t2.tname as nvarchar(20))
 FROM TestCity t1 INNER JOIN org t2
   ON t1.parentCode = t2.Code
)
SELECT code ,parentCode, tname + name  as name 
FROM org

　　

再比较以下两段从StackOverFolw中摘来的代码

--报错 Types don't match between the anchor and the recursive part in column "nm" of recursive query "cte".
WITH cte AS (
  SELECT 
    1 as rn, 
    'name1' as nm
  UNION ALL
  SELECT 
    rn + 1,
    nm = 'name' + CAST((rn + 1) as varchar(255))
  FROM cte a WHERE rn < 10)
SELECT * 
FROM cte

--正解
with cte as
(
select  1 as rn, 
        CAST('name1' AS VARCHAR(255)) as nm
union all
select  rn+1,
        nm = CAST('name' + CAST((rn+1) as varchar(255)) AS VARCHAR(255))
from cte a where rn<10)
select * from cte