定义变量以与 IN 运算符 (T-SQL) 一起使用

Define variable to use with IN operator (T-SQL)

提问by Marcos Crispino

I have a Transact-SQL query that uses the IN operator. Something like this:

我有一个使用 IN 运算符的 Transact-SQL 查询。像这样的东西：

select * from myTable where myColumn in (1,2,3,4)

Is there a way to define a variable to hold the entire list "(1,2,3,4)"? How should I define it?

有没有办法定义一个变量来保存整个列表“（1,2,3,4）”？我应该如何定义它？

declare @myList {data type}
set @myList = (1,2,3,4)
select * from myTable where myColumn in @myList

回答by LukeH

DECLARE @MyList TABLE (Value INT)
INSERT INTO @MyList VALUES (1)
INSERT INTO @MyList VALUES (2)
INSERT INTO @MyList VALUES (3)
INSERT INTO @MyList VALUES (4)

SELECT *
FROM MyTable
WHERE MyColumn IN (SELECT Value FROM @MyList)

回答by realPT

DECLARE @mylist TABLE (Id int)
INSERT INTO @mylist
SELECT id FROM (VALUES (1),(2),(3),(4),(5)) AS tbl(id)

SELECT * FROM Mytable WHERE theColumn IN (select id from @mylist)

回答by hollystyles

There are two ways to tackle dynamic csv lists for TSQL queries:

有两种方法可以处理 TSQL 查询的动态 csv 列表：

1) Using an inner select

1) 使用内部选择

SELECT * FROM myTable WHERE myColumn in (SELECT id FROM myIdTable WHERE id > 10)

2) Using dynamically concatenated TSQL

2）使用动态串联的TSQL

DECLARE @sql varchar(max)  
declare @list varchar(256)  
select @list = '1,2,3'  
SELECT @sql = 'SELECT * FROM myTable WHERE myColumn in (' + @list + ')'

exec sp_executeSQL @sql

3) A possible third option is table variables. If you have SQl Server 2005 you can use a table variable. If your on Sql Server 2008 you can even pass whole table variables in as a parameter to stored procedures and use it in a join or as a subselect in the IN clause.

3) 可能的第三个选项是表变量。如果您有 SQl Server 2005，您可以使用表变量。如果您在 Sql Server 2008 上，您甚至可以将整个表变量作为参数传递给存储过程，并在连接中使用它或作为 IN 子句中的子选择。

DECLARE @list TABLE (Id INT)

INSERT INTO @list(Id)
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4


SELECT
    * 
FROM 
    myTable
    JOIN @list l ON myTable.myColumn = l.Id

SELECT
    * 
FROM 
    myTable
WHERE
    myColumn IN (SELECT Id FROM @list)

回答by allaphor

Use a function like this:

使用这样的函数：

CREATE function [dbo].[list_to_table] (@list varchar(4000))
returns @tab table (item varchar(100))
begin

if CHARINDEX(',',@list) = 0 or CHARINDEX(',',@list) is null
begin
    insert into @tab (item) values (@list);
    return;
end


declare @c_pos int;
declare @n_pos int;
declare @l_pos int;

set @c_pos = 0;
set @n_pos = CHARINDEX(',',@list,@c_pos);

while @n_pos > 0
begin
    insert into @tab (item) values (SUBSTRING(@list,@c_pos+1,@n_pos - @c_pos-1));
    set @c_pos = @n_pos;
    set @l_pos = @n_pos;
    set @n_pos = CHARINDEX(',',@list,@c_pos+1);
end;

insert into @tab (item) values (SUBSTRING(@list,@l_pos+1,4000));

return;
end;

Instead of using like, you make an inner join with the table returned by the function:

您不使用 like，而是对函数返回的表进行内部连接：

select * from table_1 where id in ('a','b','c')

becomes

变成

select * from table_1 a inner join [dbo].[list_to_table] ('a,b,c') b on (a.id = b.item)

In an unindexed 1M record table the second version took about half the time...

在未索引的 1M 记录表中，第二个版本花费了大约一半的时间......

cheers

干杯

回答by Vova

DECLARE @myList TABLE (Id BIGINT) INSERT INTO @myList(Id) VALUES (1),(2),(3),(4);
select * from myTable where myColumn in(select Id from @myList)

Please note that for long list or production systems it's not recommended to use this way as it may be much more slower than simple INoperator like someColumnName in (1,2,3,4)(tested using 8000+ items list)

请注意，对于长列表或生产系统，不建议使用这种方式，因为它可能比简单的IN运算符someColumnName in (1,2,3,4)（使用 8000 多个项目列表测试）慢得多

回答by Vova

slight improvement on @LukeH, there is no need to repeat the "INSERT INTO": and @realPT's answer - no need to have the SELECT:

@LukeH 略有改进，无需重复“INSERT INTO”：和@realPT 的回答 - 无需选择：

DECLARE @MyList TABLE (Value INT) 
INSERT INTO @MyList VALUES (1),(2),(3),(4)

SELECT * FROM MyTable
WHERE MyColumn IN (SELECT Value FROM @MyList)

回答by Max Favilli

Starting with SQL2017 you can use STRING_SPLITand do this:

从 SQL2017 开始，您可以使用STRING_SPLIT并执行以下操作：

declare @myList nvarchar(MAX)
set @myList = '1,2,3,4'
select * from myTable where myColumn in (select value from STRING_SPLIT(@myList,','))

回答by Vilx-

No, there is no such type. But there are some choices:

不，没有这样的类型。但是有一些选择：

• Dynamically generated queries (sp_executesql)
• Temporary tables
• Table-type variables (closest thing that there is to a list)
• Create an XML string and then convert it to a table with the XML functions (really awkward and roundabout, unless you have an XML to start with)

• 动态生成的查询 (sp_executesql)
• 临时表
• 表类型变量（最接近列表的变量）
• 创建一个 XML 字符串，然后使用 XML 函数将其转换为一个表（真的很笨拙和迂回，除非你有一个 XML 开始）

None of these are really elegant, but that's the best there is.

这些都不是真正优雅的，但这是最好的。

回答by Nathan Evans

I know this is old now but TSQL => 2016, you can use STRING_SPLIT:

我知道这现在已经过时了，但是 TSQL => 2016，您可以使用 STRING_SPLIT：

DECLARE @InList varchar(255) = 'This;Is;My;List';

WITH InList (Item) AS (
    SELECT value FROM STRING_SPLIT(@InList, ';')
)

SELECT * 
FROM [Table]
WHERE [Item] IN (SELECT Tag FROM InList)

回答by Michael Reyes

If you want to do this without using a second table, you can do a LIKE comparison with a CAST:

如果您想在不使用第二个表的情况下执行此操作，可以使用 CAST 进行 LIKE 比较：

DECLARE @myList varchar(15)
SET @myList = ',1,2,3,4,'

SELECT *
FROM myTable
WHERE @myList LIKE '%,' + CAST(myColumn AS varchar(15)) + ',%'

If the field you're comparing is already a string then you won't need to CAST.

如果您要比较的字段已经是一个字符串，那么您将不需要 CAST。

Surrounding both the column match and each unique value in commas will ensure an exact match. Otherwise, a value of 1 would be found in a list containing ',4,2,15,'

围绕列匹配和逗号中的每个唯一值将确保完全匹配。否则，将在包含 ',4,2,15,' 的列表中找到值 1