Мой код SQL довольно прост. Я пытаюсь выбрать некоторые данные из базы данных следующим образом:
SELECT * FROM DBTable
WHERE id IN (1,2,5,7,10)
Я хочу знать, как объявить список перед выбором (в переменной, списке, массиве или что-то в этом роде), и внутри select только используйте имя переменной, примерно так:
VAR myList = "(1,2,5,7,10)"
SELECT * FROM DBTable
WHERE id IN myList
Вы можете объявить переменную как временную таблицу следующим образом:
declare @myList table (Id int)
Это означает, что вы можете использовать оператор insert для заполнения его значениями:
insert into @myList values (1), (2), (5), (7), (10)
Тогда ваш оператор select может использовать оператор in:
select * from DBTable
where id in (select Id from @myList)
Или вы можете присоединиться к временной таблице следующим образом:
select *
from DBTable d
join @myList t on t.Id = d.Id
И если вы сделаете что-то подобное, вы можете рассмотреть определение пользовательского типа таблицы, чтобы затем вы могли объявить свою переменную как это:
declare @myList dbo.MyTableType
Это невозможно при обычном запросе, поскольку в предложении in требуются отдельные значения, а не одно значение, содержащее список, разделенный запятыми. Одним из решений будет динамический запрос
declare @myList varchar(100)
set @myList = '(1,2,5,7,10)'
exec('select * from DBTable where id IN ' + @myList)
Вы можете преобразовать список переданных значений в параметр, оцененный в таблице, а затем выбрать в этом списке
DECLARE @list NVARCHAR(MAX)
SET @list = '1,2,5,7,10';
DECLARE @pos INT
DECLARE @nextpos INT
DECLARE @valuelen INT
DECLARE @tbl TABLE (number int NOT NULL)
SELECT @pos = 0, @nextpos = 1;
WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (number)
VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
SELECT @pos = @nextpos;
END
SELECT * FROM DBTable WHERE id IN (SELECT number FROM @tbl);
В этом примере строка, переданная в "1,2,5,7,10", разделяется запятыми, и каждое значение добавляется как новая строка в переменной таблицы @tbl. Затем это можно выбрать против использования стандартного SQL.
Если вы намерены повторно использовать эту функциональность, вы можете пойти дальше и преобразовать ее в функцию.
Если вы хотите вводить введенную запятую строку в качестве входных данных и применять ее в запросе, тогда вы можете сделать функцию вроде:
create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))
returns @temptable TABLE (items varchar(MAX))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end;
Вы можете использовать его как:
Declare @Values VARCHAR(MAX);
set @Values ='1,2,5,7,10';
Select * from DBTable
Where id in (select items from [dbo].[Split] (@Values, ',') )
В качестве альтернативы, если в качестве входных данных нет строки с запятыми, вы можете попробовать Table variable ИЛИ TableType Или Temp table как: INSERT с использованием LIST в хранимой процедуре
Мне всегда было легче инвертировать тест против списка в таких ситуациях. Например...
SELECT
field0, field1, field2
FROM
my_table
WHERE
',' + @mysearchlist + ',' LIKE '%,' + CAST(field3 AS VARCHAR) + ',%'
Это означает, что для значений, которые вы ищете, нет сложного mish-mash.
В качестве примера, если наш список был ('1,2,3'), тогда мы добавляем запятую в начало и конец нашего списка следующим образом: ',' + @mysearchlist + ','.
Мы также делаем то же самое для значения поля, которое мы ищем, и добавляем подстановочные знаки: '%,' + CAST(field3 AS VARCHAR) + ',%' (обратите внимание на символы % и ,).
Наконец, мы тестируем два, используя оператор LIKE: ',' + @mysearchlist + ',' LIKE '%,' + CAST(field3 AS VARCHAR) + ',%'.