递归查询字符相加报出类型错误

luosjz 2008-12-26 01:11:18
如下,报的错误为:类型不匹配之间的锚和递归部分栏“routes”的递归查询“tb” 。
DECLARE @T TABLE (ID INT,start VARCHAR(20),[end] VARCHAR(20))
INSERT INTO @T(ID,start,[end])
SELECT 1,'A','B' UNION ALL
SELECT 2,'B','D' UNION ALL
SELECT 3,'E','F' UNION ALL
SELECT 4,'D','M' UNION ALL
SELECT 5,'B','K' UNION ALL
SELECT 6,'G','W'

;WITH tb AS
(
SELECT
start,
[end],
routes=start+','+[end]
FROM @T WHERE start='B'
UNION ALL
SELECT
A.start,
B.[end],
A.routes+','+B.start
FROM tb AS A
JOIN @T AS B ON A.[end]=B.start
)
SELECT * FROM tb
...全文
148 点赞 收藏 4
写回复
4 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
等不到来世 2008-12-26
with中第一条select的字段长度一定要足够长。
回复
liangCK 2008-12-26
定位点成员和递归成员的类型需要一致..

所以这里将定位点的routes转换为varchar,而递归成员也转换为varchar
回复
等不到来世 2008-12-26
DECLARE @T TABLE (ID INT,start VARCHAR(20),[end] VARCHAR(20)) 
INSERT INTO @T(ID,start,[end])
SELECT 1,'A','B' UNION ALL
SELECT 2,'B','D' UNION ALL
SELECT 3,'E','F' UNION ALL
SELECT 4,'D','M' UNION ALL
SELECT 5,'B','K' UNION ALL
SELECT 6,'G','W'

;WITH tb AS
(
SELECT
start,
[end],
routes=cast(start+','+[end] as varchar(8000))
FROM @T WHERE start='B'
UNION ALL
SELECT
A.start,
B.[end],
A.routes+','+B.start
FROM tb AS A
JOIN @T AS B ON A.[end]=B.start
)
SELECT * FROM tb
回复
liangCK 2008-12-26
DECLARE @T TABLE (ID INT,start VARCHAR(20),[end] VARCHAR(20)) 
INSERT INTO @T(ID,start,[end])
SELECT 1,'A','B' UNION ALL
SELECT 2,'B','D' UNION ALL
SELECT 3,'E','F' UNION ALL
SELECT 4,'D','M' UNION ALL
SELECT 5,'B','K' UNION ALL
SELECT 6,'G','W'

;WITH tb AS
(
SELECT
start,
[end],
routes=CAST(start+','+[end] AS VARCHAR(MAX))
FROM @T WHERE start='B'
UNION ALL
SELECT
A.start,
B.[end],
CAST(A.routes+','+B.start AS VARCHAR(MAX))
FROM tb AS A
JOIN @T AS B ON A.[end]=B.start
)
SELECT * FROM tb
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-12-26 01:11
社区公告
暂无公告