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

问题描述

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

I am trying to implement search functionality with list of values in SQL variable, including range. Appreciate any guidance/links pointing to correct approach for this.

以下是数据集:

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'Boo**'),
       (N'9', N'kd', N'Book9'),
       (N'10', N'mn', N'Book10    ')
GO

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

Below I am trying to filter using the SQL IN clause but this does not return desired result.

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)

推荐答案

你不能直接使用字符串作为列表，但你可以使用 STRING_SPLIT (Transact-SQL) 如果你真的需要将过滤参数作为字符串传递:

You cannot directly use strings as lists, but you can do use STRING_SPLIT (Transact-SQL) if you really need to pass filtering parameters as strings:

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) 这样你就可以简化解析部分:

Also it might be an idea to pass your filter as json and OPENJSON (Transact-SQL) so you can make parsing part simplier:

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 或更高版本

All above, of course, only applicable if you have Sql Server 2016 or higher