定义与 IN 操作符一起使用的变量(T-SQL)

我有一个使用 IN 运算符的 Transact-SQL 查询:

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

有没有一种方法可以定义一个变量来保存整个列表“(1,2,3,4)”? 我应该如何定义它？

declare @myList {data type}
set @myList = (1,2,3,4)
select * from myTable where myColumn in @myList
234189 次浏览
小开

我认为您必须声明一个字符串，然后执行该 SQL 字符串。

看看 Sp _ ExecuteSQL

小开

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

1)使用内部选择

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

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

第三种可能的选择是表变量。如果有 SQLServer2005，则可以使用表变量。如果您使用的是 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)
小开 
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)
小开

不，没有这种类型。但是有一些选择:

  • 动态生成的查询(sp _ Executesql)
  • 临时桌子
  • 表类型变量(最接近列表的东西)
  • 创建一个 XML 字符串，然后将其转换为具有 XML 函数的表(非常笨拙和迂回，除非您首先使用 XML)

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

小开

使用如下函数:

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;

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

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

变成了

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

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

小开 
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)
小开

如果您不想使用第二个表，那么可以使用一个 CAST 进行 LIKE 比较:

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


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

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

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

小开 
DECLARE @StatusList varchar(MAX);
SET @StatusList='1,2,3,4';
DECLARE @Status SYS_INTEGERS;
INSERT INTO  @Status
SELECT Value
FROM dbo.SYS_SPLITTOINTEGERS_FN(@StatusList, ',');
SELECT Value From @Status;
小开 
DECLARE @myList TABLE (Id BIGINT) INSERT INTO @myList(Id) VALUES (1),(2),(3),(4);
select * from myTable where myColumn in(select Id from @myList)

请注意，对于长列表或生产系统，不推荐使用这种方式，因为它可能比简单的 IN操作符(如 someColumnName in (1,2,3,4))慢得多(使用8000 + 项目列表进行测试)

小开

在@LukeH 上稍有改进，就不需要重复“ INSERT INTO”: 以及@realPT 的回答——不需要 SELECT:

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


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

我知道这已经过时了，但是 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)
小开

从 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,','))
小开

之前没有人提到过，从 Sql Server 2016开始，您还可以使用 json 数组和 OPENJSON (Transact-SQL):

declare @filter nvarchar(max) = '[1,2]'


select *
from dbo.Test as t
where
exists (select * from openjson(@filter) as tt where tt.[value] = t.id)

你可以测试一下 sql fiddle demo

您也可以使用 json 更容易地覆盖更复杂的情况-请参阅 使用 WHERE IN 子句和 SQL 变量搜索 SQL 中的值和范围列表？

小开

这个函数使用 PATINDEX 将表中的 id 与非数字分隔的整数列表匹配。

-- Given a string @myList containing character delimited integers
-- (supports any non digit delimiter)
DECLARE @myList VARCHAR(MAX) = '1,2,3,4,42'


SELECT * FROM [MyTable]
WHERE
-- When the Id is at the leftmost position
-- (nothing to its left and anything to its right after a non digit char)
PATINDEX(CAST([Id] AS VARCHAR)+'[^0-9]%', @myList)>0
OR
-- When the Id is at the rightmost position
-- (anything to its left before a non digit char and nothing to its right)
PATINDEX('%[^0-9]'+CAST([Id] AS VARCHAR), @myList)>0
OR
-- When the Id is between two delimiters
-- (anything to its left and right after two non digit chars)
PATINDEX('%[^0-9]'+CAST([Id] AS VARCHAR)+'[^0-9]%', @myList)>0
OR
-- When the Id is equal to the list
-- (if there is only one Id in the list)
CAST([Id] AS VARCHAR)=@myList

备注:

  • 当转换为 varchar 并且不在括号中指定字节大小时，默认长度为30
  • % (通配符)将匹配任何零个或多个字符的字符串
  • ^ (通配符)不匹配
  • [ ^ 0-9]将匹配任何非数字字符
  • PATINDEX 是一个 SQL 标准函数，它返回模式在字符串中的位置
小开

其中大多数似乎侧重于将每个 INT 分离到它自己的插入语中，例如:

  • (1)、(2)、(3)等等..。

这并不总是方便的，特别是因为很多时候，你已经用逗号分隔的列表开始了，例如:

  • (1,2,3，...)等等..。

在这些情况下，您可能会更愿意这样做:

DECLARE @ListOfIds TABLE (DocumentId INT);


INSERT INTO @ListOfIds
SELECT Id FROM [dbo].[Document] WHERE Id IN (206,235,255,257,267,365)


SELECT * FROM @ListOfIds

我喜欢这个方法，因为我经常尝试使用表中应该已经存在的 ID。

小开

根据我的经验，这里提供了一种常见的技术,

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

如果主数据表(Mytable)包含大量记录，则会导致性能严重下降。据推测，这是因为对于数据表中的每条记录，IN 操作符的 list-subquery 都要重新执行。 我在这里没有看到任何提供的解决方案通过完全避免 IN 操作符来提供相同的功能结果。通常的问题不是需要参数化 IN 操作，而是需要参数化包含约束。我最喜欢的方法是使用一个(内部)连接来实现它:

DECLARE @myList varchar(50) /* BEWARE: if too small, no error, just missing data! */
SET @myList = '1,2,3,4'


SELECT *
FROM myTable
JOIN STRING_SPLIT(@myList,',') MyList_Tbl
ON myColumn = MyList_Tbl.Value

因为约束列表(MyList _ Tbl)的生成在整个查询执行过程中只执行一次，所以速度快得多。通常，对于大型数据集，此技术的执行速度至少是功能等效的参数化 IN 运算符解决方案(如本文提供的解决方案)的5倍。


提交答案