問題描述

我正在嘗試使用 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 中搜索包括范圍的值列表?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！