使用带有 SQL 变量的 WHERE IN 子句搜索值列表，包括 SQL 中的范围?

Question

我正在尝试使用 SQL 变量中的值列表(包括范围)实现搜索功能。感谢任何指向正确方法的指导/链接。

以下是数据集：

CREATE TABLE [dbo].[Books]
(
[ID] [NCHAR](10) NOT NULL,
[AUTHCODE] [NCHAR](10) NULL,
[TITLE] [NCHAR](10) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Books] ([ID], [AUTHCODE], [TITLE]) 
VALUES (N'1', N'nk', N'Book1'), 
(N'2', N'an', N'Book2'),
(N'3', N'mn', N'Book3'),
(N'4', N'ra', N'Book4'),
(N'5', N'kd', N'Book5'),
(N'6', N'nk', N'Book6'),
(N'7', N'an', N'Book7'),
(N'8', N'ra', N'Book8'),
(N'9', N'kd', N'Book9'),
(N'10', N'mn', N'Book10    ')
GO

下面我尝试使用 SQL IN 子句进行过滤，但这不会返回所需的结果。

select * from books
declare @List1 varchar(max) = '2,4,6,7,8,9' --simple list
select * 
from books
where id in (@List1)
declare @List2 varchar(max) = '2,4-7,9' --list with range
select * 
from books
where id in (@List2)

Answer 1

您不能直接将字符串用作列表，但如果确实需要将过滤参数作为字符串传递，则可以使用STRING_SPLIT (Transact-SQL)：

declare @list varchar(max) = '2,4,6-8,9'
declare @filter table (id1 int, id2 int)
insert into @filter (id1,id2)
select
case when b.pos > 0 then left(a.[value], pos - 1) else a.[value] end as id1,
case when b.pos > 0 then right(a.[value], len(a.[value]) - pos) else a.[value] end as id2
from string_split(@list, ',') as a
cross apply (select charindex('-', a.[value]) as pos) as b
select *
from [dbo].[Books] as b
where
exists (select * from @filter as tt where b.id between tt.id1 and tt.id2)

此外，将过滤器作为 json 和OPENJSON (Transact-SQL)传递可能是一个想法，这样您就可以简化解析部分：

declare @list varchar(max) = '[2,4,[6,8],9]'
select
case when a.[type] = 4 then json_value(a.[value], '$[0]') else a.[value] end,
case when a.[type] = 4 then json_value(a.[value], '$[1]') else a.[value] end
from openjson(@list) as a

当然，以上所有内容仅适用于拥有 Sql Server 2016 或更高版本的情况。

Answer 2

IN()运算符确定指定的值是否与子查询或列表中的任何值匹配。 不是字符串，而是你在做什么。

您正在尝试做的事情可以通过以下方式完成

DECLARE @List1 AS TABLE (ID INT); 
INSERT INTO @List1 
SELECT 2 UNION SELECT 4 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9;
SELECT *
FROM Books
WHERE ID IN (SELECT ID FROM @List1);
DECLARE @List2 As TABLE (AFrom INT, ATo INT);
INSERT INTO @List2
SELECT 2, 4 UNION SELECT 7, 9;
SELECT *
FROM Books B CROSS APPLY @List2 L
WHERE B.ID BETWEEN L.AFrom AND L.ATo;

现场演示