SQL CTE 错误：“锚点和递归部分之间的类型不匹配”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1838276/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CTE error: "Types don't match between the anchor and the recursive part"
提问by priyanka.sarkar
I am executing the following statement:
我正在执行以下语句：
;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
...which finishes with the error...
...以错误结束...
Msg 240, Level 16, State 1, Line 2
Types don't match between the anchor and the recursive part in column "nm" of recursive query "cte".
Where am I making the mistake?
我在哪里犯了错误？
回答by gbn
Exactly what it says:
正是它所说的：
'name1'has a different data type to 'name' + CAST((rn+1) as varchar(255))
'name1'具有不同的数据类型 'name' + CAST((rn+1) as varchar(255))
Try this (untested)
试试这个（未经测试）
;with cte as
(
select 1 as rn, CAST('name1' as varchar(259)) as nm
union all
select rn+1,nm = 'name' + CAST((rn+1) as varchar(255))
from cte a where rn<10)
select * from cte
Basically, you have to ensure the length matches too. For the recursive bit, you may have to use CAST('name' AS varchar(4))if it fails again
基本上，您也必须确保长度匹配。对于递归位，CAST('name' AS varchar(4))如果再次失败可能需要使用
回答by Adriaan Stander
You need to cast both nm fields
您需要同时投射两个 nm 场
;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
回答by Vitaly Borisov
For me problem was in different collation.
对我来说，问题在于不同的整理。
Only this helped me:
只有这对我有帮助：
;WITH cte AS (
SELECT
1 AS rn,
CAST('name1' AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS nm
UNION ALL
SELECT
rn + 1,
nm = CAST('name' + CAST((rn + 1) AS NVARCHAR(255)) AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT
FROM cte a WHERE rn < 10)
SELECT *
FROM cte;
Hope it can help someone else.
希望它可以帮助别人。
回答by priyanka.sarkar
;with cte as
(
select 1 as rn, 'name' + CAST(1 as varchar(255)) as nm
union all
select rn+1,nm = 'name' + CAST((rn+1) as varchar(255))
from cte a where rn<10)
select * from cte
回答by BusinessAlchemist
In my case, I messed up the sequence of columns in top and bottom clauses of UNION ALL. And it turned out that a varcharcolumn appeared 'under' an intone. An easy mistake to make of you have lots of columns
就我而言，我弄乱了UNION ALL. 事实证明，一个varchar列出现在一个列的“下方” int。一个很容易犯的错误是你有很多列
回答by Matthew Smithson
I would recommend using nvarchar(max)
我建议使用 nvarchar(max)
WITH CTE AS (
SELECT x,x_name FROM (VALUES (1,CAST('' AS nvarchar(MAX)))) AS test(x,x_name)
UNION ALL
SELECT x + 1 x, CONCAT(x_name,x+1) FROM CTE WHERE x < 10 )
SELECT * FROM CTE
回答by Ludovic Aubert
If you use CONCAT in the recursive term of a rcte, since the output type of concat is varchar(MAX), you only need to cast the column in the initial query:
如果在 rcte 的递归项中使用 CONCAT，由于 concat 的输出类型是 varchar(MAX)，因此只需在初始查询中强制转换列：
WITH rcte AS (
SELECT 1 AS nr, CAST('1' AS varchar(MAX)) AS trail
UNION ALL
SELECT nr+1, CONCAT(trail, '/', nr+1)
FROM rcte
WHERE nr < 5
)
SELECT * FROM rcte;
回答by bhuneshwar singh
WITH rcte AS (
SELECT 1 AS nr, CAST('1' AS varchar(MAX)) AS trail
UNION ALL
SELECT nr+1, cast(CONCAT(trail, '/', nr+1) as varchar(max))
FROM rcte
WHERE nr < 5
)
SELECT * FROM rcte;
回答by Gajendra Singh
;with tmp1(NewsId,DataItem ,HeaderText)
as
(
select NewsId, LEFT(HeaderText, CHARINDEX(',',HeaderText+',')-1),
STUFF(HeaderText, 1, CHARINDEX(',',HeaderText+','), '')
from Currentnews
union all
select NewsId, LEFT(HeaderText, CHARINDEX(',',HeaderText+',')-1),
STUFF(HeaderText, 1, CHARINDEX(',',HeaderText+','), '')
from tmp1
where HeaderText > ''
)
select NewsId, DataItem
from tmp1
order by NewsId
