通常用来实现树形结构
首先来看一下表数据
以下代码是递归查询北京市的树结构
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