求一个SQL语句 写法！

[恶魔的幻影]

如何 让语句 "一" 查询出来的值等于语句 "二" 的ID！
现在我自己简单弄了下 但是会提示 类型不符
SELECT * FROM userinfo
WHERE id IN (SELECT chanyurenid FROM hr_Training_records WHERE id=131)

现在语句一、
SELECT chanyurenid FROM hr_Training_records WHERE id=131
查询结果集：
chanyurenid (nvarchar)
122,195,196,193,215,216

语句二、
SELECT id FROM userinfo 
查询结果集：
id  （int）  
122
195
196
193
215
216

[xdashewan]

nvarchar和int无法用来直接比较，需要其中一个做类型转换

[Mr_Nice]

SELECT  *
FROM    userinfo U
WHERE   EXISTS ( SELECT 1
                 FROM   hr_Training_records H
                 WHERE  CHARINDEX(chanyurenid, CONVERT(VARCHAR, U.id)) > 0 )   --转换一下

[chen357313771]

IF OBJECT_ID('tempdb..#temp','U') IS NOT NULL DROP TABLE #temp
CREATE TABLE #temp
(
	chanyurenid nvarchar(100)
)
INSERT INTO #temp VALUES ('122,195,196,193,215,216')

SELECT  T.c.value('.','int') AS chanyurenid
FROM (
SELECT CONVERT(XML,'<root><v>'+REPLACE(A.chanyurenid,',','</v><v>')+'</v></root>')  AS id
FROM #temp AS A ) AS A
CROSS APPLY A.id.nodes('root/v') T(c)

[恶魔的幻影]

引用 2 楼 OrchidCat 的回复:

SELECT  *
FROM    userinfo U
WHERE   EXISTS ( SELECT 1
                 FROM   hr_Training_records H
                 WHERE  CHARINDEX(chanyurenid, CONVERT(VARCHAR, U.id)) > 0 )   --转换一下

怎么没看明白呢，查询出来也没有数据!

[恶魔的幻影]

引用 3 楼 chen357313771 的回复:

IF OBJECT_ID('tempdb..#temp','U') IS NOT NULL DROP TABLE #temp
CREATE TABLE #temp
(
	chanyurenid nvarchar(100)
)
INSERT INTO #temp VALUES ('122,195,196,193,215,216')

SELECT  T.c.value('.','int') AS chanyurenid
FROM (
SELECT CONVERT(XML,'<root><v>'+REPLACE(A.chanyurenid,',','</v><v>')+'</v></root>')  AS id
FROM #temp AS A ) AS A
CROSS APPLY A.id.nodes('root/v') T(c)

你这个语句太复杂了啊！而且提示出错！

[恶魔的幻影]

引用 2 楼 OrchidCat 的回复:

SELECT  *
FROM    userinfo U
WHERE   EXISTS ( SELECT 1
                 FROM   hr_Training_records H
                 WHERE  CHARINDEX(chanyurenid, CONVERT(VARCHAR, U.id)) > 0 )   --转换一下

这个语句只针对单条数据，但是多条的就不行了 比如122,195,196,193,215,216 我需要的是多条的这种！

[哥眼神纯洁不]

引用 4 楼 aqiang566971 的回复:

[quote=引用 2 楼 OrchidCat 的回复:]

SELECT  *
FROM    userinfo U
WHERE   EXISTS ( SELECT 1
                 FROM   hr_Training_records H
                 WHERE  CHARINDEX(chanyurenid, CONVERT(VARCHAR, U.id)) > 0 )   --转换一下

怎么没看明白呢，查询出来也没有数据![/quote] 写反了 SELECT * FROM userinfo U WHERE EXISTS ( SELECT 1 FROM hr_Training_records H where charindex(','+convert(varchar,u.id)+',',','+chanyurenid+',')>0)

[唐诗三百首]

select * 
 from userinfo a
 where exists 
 (select 1 
  from hr_Training_records b
  where b.id=131 and charindex(','+rtrim(a.id)+',',','+b.chanyurenid+',',1)>0)