一张表结构是这样子的:
id(int) content(char) nextid(char)
1 内容1 2, 3, 4
2 内容2 23, 29, 89就依这两行数据为例:
现在我想查询出1,2,3,4,23,29,89行
用这语句会报错select *from table id in(select nextid from table)
错误类型:将 char 值 '2, 3, 4转换为数据类型为 int 的列时发生语法错误。怎么写才能实现?谢谢了
id(int) content(char) nextid(char)
1 内容1 2, 3, 4
2 内容2 23, 29, 89就依这两行数据为例:
现在我想查询出1,2,3,4,23,29,89行
用这语句会报错select *from table id in(select nextid from table)
错误类型:将 char 值 '2, 3, 4转换为数据类型为 int 的列时发生语法错误。怎么写才能实现?谢谢了
?
select * from table id in(select cast(nextid as int) id from table)
from table t1, table t2
where charindex(','+ltrim(t1.id)+',',','+replace(t2.nextid,' ','')+',')>0
INSERT INTO #temp
SELECT 1, N'内容1', '2, 3, 4' UNION ALL
SELECT 2, N'内容2', '23, 29, 89' UNION ALL
SELECT 89,N'内容3', ''SELECT * FROM #temp
;WITH CTE_A AS
(
SELECT id, [Content], nextid FROM #temp WHERE id = 1
UNION ALL
SELECT b.id, b.[Content], b.nextid FROM CTE_A a, #temp b WHERE CHARINDEX(', ' + CAST(b.id AS VARCHAR(10)) + ', ', ', '+a.nextid+', ') > 0
)
SELECT * FROM CTE_A
and t2.id in (1,2)
不过还少一个1,该怎么办?