“IN”的问题sql server中的运算符 [英] problem with "IN" Operator in sql server
本文介绍了“IN”的问题sql server中的运算符的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的sql命令是这样的:
DECLARE @ str nvarchar = ' 1,2,3,4,5,6,7'
SELECT * FROM mytable
WHERE ID IN ( @str )
但结果是第一行(id = 1)
如何检索所有行(1,2,3,4,5,6,7)
i使用sql server 2008
解决方案
不幸的是,你不能那么简单 - 它只是不起作用!
你必须绕过房子并将CSV转换为SQL不可靠的列表 - 在练习某种形式的表格。我是这样做的:
DECLARE @INSTR as VARCHAR (MAX)
SET @ INSTR = ' 2,3,177,'
DECLARE @ SEPERATOR as VARCHAR ( 1 )
DECLARE @ SP INT
DECLARE @ VALUE VARCHAR ( 1000 )
SET @ SEPERATOR = ' ,'
CREATE TABLE #tempTab(id int 不 null )
WHILE PATINDEX(' %' + @ SEPERATOR + ' %', @ INSTR )<> 0
BEGIN
SELECT @ SP = PATINDEX(' %' + @ SEPERATOR + ' %', @ INSTR )
SELECT @ VALUE = LEFT ( @ INSTR , @ SP - 1 )
SELECT @INSTR = STUFF( @ INSTR , 1 , @ SP ,' ')
INSERT INTO #tempTab(id) VALUES ( @ VALUE )
END
SELECT * FROM myTable WHERE id IN ( SELECT id FROM #tempTab)
DROP 表 #tempTab这不好,但它有效! :笑:
嗨...
尝试动态查询如下。
DECLARE @ str varchar ( 100 )= ' 1,2,3,4,5,6,7', @ SqlStr VARCHAR ( 1000 )
CREATE TABLE mytable(ID INT )
INSERT INTO mytable(ID) VALUES ( 1 ),(< span class =code-digit> 2 ),( 3 ),( 4 ),( 5 ),( 6 ),( 7 )
SELECT @ Sqlstr = ' SELECT * FROM mytable
WHERE ID IN(' + @ str + ' )'
PRINT @ SqlStr
EXEC ( @ SqlStr )
DROP TABLE mytable
问候,
GVPrabu
CREATE FUNCTION [dbo]。[SplitDelimited]
(
@ List nvarchar (MAX),
@ SplitOn nvarchar ( 20 )
)
RETURNS @ RtnValue < span class =code-keyword> table (
Value nvarchar ( 100 )
)
AS
BEGIN
< span class =code-keyword> while (Charindex( @ SplitOn , @ List )> 0)
开始
插入 进入 @ RtnValue (值)
选择
Value = ltrim(rtrim(Substring( @ List , 1 ,Charindex( @ SplitOn , @ List ) - 1)))
Set @ List = Substring( @ List ,Charindex( @SplitOn , @ List )+ len( @ SplitOn ),len( @ List ))
结束
插入 进入 @ RtnValue (值)
选择值= ltrim(rtrim( @ List ))
返回
END
DECLARE < span class =code-sdkkeyword> @ str nvarchar (max)= ' < span class =code-string> 1,2,3,4,5,6,7'
SELECT * FROM myTable WHERE id IN ( SELECT 值 FROM SplitDelimited( @ str ))
hi,
my sql command is like this:
DECLARE @str nvarchar='1,2,3,4,5,6,7'
SELECT * FROM mytable
WHERE ID IN (@str)
but the result is the first row(id=1)
how can retrieve all row (1,2,3,4,5,6,7)
i use sql server 2008
解决方案
Unfortunately, you can''t do it that simply - it just doesn''t work!
You have to go a bit round the houses and convert the CSV to a list that SQL underatsnds - in practice a table of some form. Here''s how I do it:
DECLARE @INSTR as VARCHAR(MAX) SET @INSTR = '2,3,177,' DECLARE @SEPERATOR as VARCHAR(1) DECLARE @SP INT DECLARE @VALUE VARCHAR(1000) SET @SEPERATOR = ',' CREATE TABLE #tempTab (id int not null) WHILE PATINDEX('%' + @SEPERATOR + '%', @INSTR ) <> 0 BEGIN SELECT @SP = PATINDEX('%' + @SEPERATOR + '%',@INSTR) SELECT @VALUE = LEFT(@INSTR , @SP - 1) SELECT @INSTR = STUFF(@INSTR, 1, @SP, '') INSERT INTO #tempTab (id) VALUES (@VALUE) END SELECT * FROM myTable WHERE id IN (SELECT id FROM #tempTab) DROP TABLE #tempTabIt''s not nice, but it works! :laugh:
Hi...
Try Dynamic Query as follows.
DECLARE @str varchar(100)='1,2,3,4,5,6,7', @SqlStr VARCHAR(1000) CREATE TABLE mytable (ID INT) INSERT INTO mytable(ID) VALUES(1),(2),(3),(4), (5), (6), (7) SELECT @Sqlstr='SELECT * FROM mytable WHERE ID IN ('+@str+')' PRINT @SqlStr EXEC(@SqlStr) DROP TABLE mytable
Regards,
GVPrabu
CREATE FUNCTION [dbo].[SplitDelimited] ( @List nvarchar(MAX), @SplitOn nvarchar(20) ) RETURNS @RtnValue table ( Value nvarchar(100) ) AS BEGIN While (Charindex(@SplitOn,@List)>0) Begin Insert Into @RtnValue (value) Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List)) End Insert Into @RtnValue (Value) Select Value = ltrim(rtrim(@List)) Return END DECLARE @str nvarchar(max)='1,2,3,4,5,6,7' SELECT * FROM myTable WHERE id IN (SELECT Value FROM SplitDelimited(@str))
这篇关于“IN”的问题sql server中的运算符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文