问题描述
我正在尝试使用 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