--你查查in的语法就知道原因了.--正确的写法是这样的:select * from [user] where charindex(','+cast(userid as varchar)+',',(select ','+userids+',' from user_depart where depart='2'))>0
select * from user where (select ','+rtrim(userids)+',' from user_depart where depart='2') like '%,'''+cast(userid as varchar(10))+''',%'能节省空间吗?麻烦倒不少,改吧
select * from user where exists(select userids from user_depart where depart='2' and user_depart.userids=user.userid)
declare @s varchar(50) declare @sql varchar(100) set @s='' select @s=@s+userids+',' from user_depart where depart='2'set @sql='select * from user where userid in ('+@s set @sql=left(@sql,len(@sql)-1); set @sql=@sql+')' exec(@sql)
where exists(select userids
from user_depart
where depart='2' and user_depart.userids=user.userid)
2 101
2 103
2 105
这样的话每个人最多可能有100条记录
而且总员工人数有600多人,那不是要60000多条记录吗,有什么好的方法呢
declare @sql varchar(100)
set @s=''
select @s=@s+userids+',' from user_depart where depart='2'set @sql='select * from user where userid in ('+@s
set @sql=left(@sql,len(@sql)-1);
set @sql=@sql+')'
exec(@sql)
听了大家的意见,还是用多条记录来处理,Thanks all!