使用带有 SQL 变量的 WHERE IN 子句在 SQL 中搜索包括范围的值列表? [英] Search list of values including range in SQL using WHERE IN clause with SQL variable?

问题描述

我正在尝试使用 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'Book8'),
       (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

这篇关于使用带有 SQL 变量的 WHERE IN 子句在 SQL 中搜索包括范围的值列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！