FROM t_Profile
where profileID in (
SELECT top 1 ProfileCase
FROM t_EncodeFile
order by EncodeFileID desc
)
其中ProfileCase是字符型
profileID是int型
我这样执行后,出错
将 varchar 值 '1,2,3,4,5,6' 转换为数据类型为 int 的列时发生语法错误。
8 个解决方案
#1
up
#2
DECLARE @str nvarchar(255)
set @str='1,2,3,4,5,6'
SELECT ProfileName , ProfileType
FROM t_Profile
where profileID in ( @str )
我这样执行 也报同样的错误!
set @str='1,2,3,4,5,6'
SELECT ProfileName , ProfileType
FROM t_Profile
where profileID in ( @str )
我这样执行 也报同样的错误!
#3
算了,只好用分离字符串的方法了。
declare @str varchar(300),@id varchar(300),@m int,@n int
set @str='a,b,c,d,e'
set @str=@str + ','
set @m=CHARINDEX(',',@str)
set @n=1
WHILE @m>0
BEGIN
set @id=substring(@str,@n,@m-@n)
print @id
set @n=@m+1
set @m=CHARINDEX(',',@str,@n)
END
declare @str varchar(300),@id varchar(300),@m int,@n int
set @str='a,b,c,d,e'
set @str=@str + ','
set @m=CHARINDEX(',',@str)
set @n=1
WHILE @m>0
BEGIN
set @id=substring(@str,@n,@m-@n)
print @id
set @n=@m+1
set @m=CHARINDEX(',',@str,@n)
END
#4
http://community.csdn.net/Expert/FAQ/FAQ_Index.asp?id=2817
#5
--in子句使用的是表达式列表,表达式列表是由多个表达式组成
--ProfileCase只是表达式列表中的一个表达式,而不是表达式列表,所以错了.
--改为:
SELECT ProfileName , ProfileType
FROM t_Profile
where charindex(','+rtrim(profileID+',',','+(
SELECT top 1 ProfileCase
FROM t_EncodeFile
order by EncodeFileID desc
)+',')>0
--ProfileCase只是表达式列表中的一个表达式,而不是表达式列表,所以错了.
--改为:
SELECT ProfileName , ProfileType
FROM t_Profile
where charindex(','+rtrim(profileID+',',','+(
SELECT top 1 ProfileCase
FROM t_EncodeFile
order by EncodeFileID desc
)+',')>0
#6
SELECT ProfileName , ProfileType
FROM t_Profile
where profileID in (
SELECT top 1 replace(ProfileCase,'''','')
FROM t_EncodeFile
order by EncodeFileID desc
)
FROM t_Profile
where profileID in (
SELECT top 1 replace(ProfileCase,'''','')
FROM t_EncodeFile
order by EncodeFileID desc
)
#7
SELECT ProfileName , ProfileType
FROM t_Profile
where charindex(','+rtrim(profileID)+',',','+(
SELECT top 1 ProfileCase
FROM t_EncodeFile
order by EncodeFileID desc
)+',')>0
FROM t_Profile
where charindex(','+rtrim(profileID)+',',','+(
SELECT top 1 ProfileCase
FROM t_EncodeFile
order by EncodeFileID desc
)+',')>0
#8
邹大哥果然厉害。
谢谢 freddy2003兄,把括号加上去了。
谢谢 freddy2003兄,把括号加上去了。
#1
up
#2
DECLARE @str nvarchar(255)
set @str='1,2,3,4,5,6'
SELECT ProfileName , ProfileType
FROM t_Profile
where profileID in ( @str )
我这样执行 也报同样的错误!
set @str='1,2,3,4,5,6'
SELECT ProfileName , ProfileType
FROM t_Profile
where profileID in ( @str )
我这样执行 也报同样的错误!
#3
算了,只好用分离字符串的方法了。
declare @str varchar(300),@id varchar(300),@m int,@n int
set @str='a,b,c,d,e'
set @str=@str + ','
set @m=CHARINDEX(',',@str)
set @n=1
WHILE @m>0
BEGIN
set @id=substring(@str,@n,@m-@n)
print @id
set @n=@m+1
set @m=CHARINDEX(',',@str,@n)
END
declare @str varchar(300),@id varchar(300),@m int,@n int
set @str='a,b,c,d,e'
set @str=@str + ','
set @m=CHARINDEX(',',@str)
set @n=1
WHILE @m>0
BEGIN
set @id=substring(@str,@n,@m-@n)
print @id
set @n=@m+1
set @m=CHARINDEX(',',@str,@n)
END
#4
http://community.csdn.net/Expert/FAQ/FAQ_Index.asp?id=2817
#5
--in子句使用的是表达式列表,表达式列表是由多个表达式组成
--ProfileCase只是表达式列表中的一个表达式,而不是表达式列表,所以错了.
--改为:
SELECT ProfileName , ProfileType
FROM t_Profile
where charindex(','+rtrim(profileID+',',','+(
SELECT top 1 ProfileCase
FROM t_EncodeFile
order by EncodeFileID desc
)+',')>0
--ProfileCase只是表达式列表中的一个表达式,而不是表达式列表,所以错了.
--改为:
SELECT ProfileName , ProfileType
FROM t_Profile
where charindex(','+rtrim(profileID+',',','+(
SELECT top 1 ProfileCase
FROM t_EncodeFile
order by EncodeFileID desc
)+',')>0
#6
SELECT ProfileName , ProfileType
FROM t_Profile
where profileID in (
SELECT top 1 replace(ProfileCase,'''','')
FROM t_EncodeFile
order by EncodeFileID desc
)
FROM t_Profile
where profileID in (
SELECT top 1 replace(ProfileCase,'''','')
FROM t_EncodeFile
order by EncodeFileID desc
)
#7
SELECT ProfileName , ProfileType
FROM t_Profile
where charindex(','+rtrim(profileID)+',',','+(
SELECT top 1 ProfileCase
FROM t_EncodeFile
order by EncodeFileID desc
)+',')>0
FROM t_Profile
where charindex(','+rtrim(profileID)+',',','+(
SELECT top 1 ProfileCase
FROM t_EncodeFile
order by EncodeFileID desc
)+',')>0
#8
邹大哥果然厉害。
谢谢 freddy2003兄,把括号加上去了。
谢谢 freddy2003兄,把括号加上去了。