如下,报的错误为:类型不匹配之间的锚和递归部分栏“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
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
解决方案 »
- 海量数据(TB级)sqlserver2008能够承受吗?
- 江湖救济阿,帮我看看这句sql应该怎么写?
- sql server2000的存储过程存放在sysobjects表中,当不知道存储过程的参数存放在哪个表中
- SQL数据库名"YY"但程序找出为 "Local"如何修正或有其他写法将SQL数据库名变成"YY"程序如下
- 存储过程 输出参数问题?
- sql7.0 的数据怎么不能导入(DTS)到 sql 2000 中
- sql server 字符串比较、整型比较,那一个效率高?为什么?
- 这么简单的问题怎么没有人回答?
- 如何用sql语句修改表中字段的类型?
- 多表连接问题
- 怎样实现使用一个SQL查询.
- otl插入大量数据遇到的问题 在线等
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
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