Вопрос по sql, sql-server – Как использовать список значений через запятую в качестве фильтра в T-SQL?

6

У меня есть простой SQL-запрос, начинающийся с:

<code>SELECT top 20 application_id, [name], location_id FROM apps
</code>

Теперь я хотел бы закончить, чтобы он сделал это (написано в псевдокоде)

<code>if @lid > 0 then
    WHERE location_id IN (@lid)
else
    WHERE location_id is all values in location_id column
</code>

По запросу, вот пример

<code>application_id             name               location_id
----------------------------------------------------------
1                          Joe Blogs          33
2                          Sam Smith          234
3                          Jeremy Carr        33
</code>

@ locid - это результаты, предоставленные пользователем, например, '33, 234 '

If @lid пуст, тогда я бы хотел вывести все строки для location_id с именами и application_id. В противном случае я хотел бы, чтобы он выводил все строки относительно предоставленных чисел в @lid (обозначая location_id.

Так что, если @lid равен 0:

<code>application_id             name               location_id
----------------------------------------------------------
1                          Joe Blogs          33
2                          Sam Smith          234
3                          Jeremy Carr        33
</code>

В противном случае, если @lid содержит '33'

<code>application_id             name               location_id
----------------------------------------------------------
1                          Joe Blogs          33
3                          Jeremy Carr        33
</code>
Не могли бы вы предоставить пример данных и желаемый набор результатов? Quassnoi
Надеюсь, тебе этого хватит. Заранее спасибо Mike B

Ваш Ответ

10   ответов
3

Случа, который служит цели IIF или троичного оператора. Пожалуйста, проверьте эту ссылкуhttp: //msdn.microsoft.com/en-us/library/ms181765.asp

Возгласы

СЛУЧАЙ, КОГДА MyNum <10, ТОГДА 'Маленький', ДРУГОЙ 'Большой' КОНЕЦ Ash Machine
Я пробовал дело, но результаты были неоднозначными. Не могли бы вы предоставить пример кода, который я мог бы попробовать на моих примерах? Mike B
3

@locid и @lid являютсяодно и тож, Я собираюсь использовать @locid ... Следующее будет работать. Я разделил его, чтобы он выглядел хорошо на SO.

SELECT application_id, [name], location_id 
FROM apps
WHERE
  ( 
    @locid = 0
    OR 
    CHARINDEX
    ( 
      ',' + CAST(location_id AS VARCHAR(50)) + ','
      , 
      ',' + @locid + ','
      , 
      0 
    ) > 0
  )
Как я могу обрезать лишние пробелы в CSV? Imran Rizvi
3

например, «33, 234» и т. Д., Тогда никакое решение здесь не будет работать. Тем не менее, я думаю, что они были опубликованы до вашего обновления с этой информацией.

Я полагаю, потому что ты это сказал:

@ locid - это результаты, предоставленные пользователем, например, '33, 234 '

Вы не можете развернуть переменную напрямую, чтобыlocation_in IN (33, 234). Вы на самом деле проситеlocation_id = '33, 234', которая завершится неудачно с преобразованием CAST из-за приоритета типа данных.

Сначала нужно разобрать список в табличную форму для использования в конструкции JOIN / EXISTS. Есть несколько вариантов, и Эрланд описывает их все здесь: Массивы и списки в SQL Server 2005

Вы правы, и я уже проанализировал этот varchar-список, чтобы он уже проверял каждое значение как форму использования нескольких значений в одном операторе select. Mike B
2

IN со списком через запятую: SQL Server

Если ваш@lid - это список целых чисел через запятую, используйте это:

WITH    cd AS
        (
        SELECT  1 AS first, CHARINDEX(',', @lid, 1) AS next
        UNION ALL
        SELECT  next + 1, CHARINDEX(',', @lid, next + 1)
        FROM    cd
        WHERE   next > 0
        ),
        lid AS
        (
        SELECT  CAST(SUBSTRING(@lid, first, CASE next WHEN 0 THEN LEN(@lid) + 1 ELSE next END - first)AS INT) AS id
        FROM    cd
        )
SELECT  d.*
FROM    (
        SELECT  DISTINCT id
        FROM    lid
        ) l
JOIN    apps a
ON      a.location_id = l.id
        AND @lid <> '0'
UNION ALL
SELECT  *
FROM    apps a
WHERE   @lid = '0'

Это намного эффективнее, чем использованиеOR конструирует.

@ Эрик: Спасибо. Да, SQL Server не может хорошо оптимизировать ИЛИ. Однако ваш запрос будет хорошо работать в MySQL: он заметит, что @lid не является нулем, и полностью удалит предикат из плана. Quassnoi
Удалил мой ответ и дал вам +1 после некоторых тестов конструкции OR с некоторыми большими наборами данных. Мне никогда не удавалось понять, насколько они неэффективны (используются только для небольших наборов данных). В любом случае, я думаю, что последний запрос - это то, что ищет ОП. Eric
Моя цель состоит в том, чтобы вернуть все строки в столбце location_id, если в @lid ничего нет, поэтому будут получены те же результаты, что и в случае «SELECT location_id from inquiries». Если в @lid ничего нет, я хочу, чтобы он выполнял поиск каждого location_id в базе данных. Mike B
2
WHERE CHARINDEX(LocationId, @lid) > 1
, если вы передадите «33», вы получите неверные результаты, вернет идентификатор 33, а также идентификатор 3, ответ «Бретт Веенстра» ниже исправляет это Abou-Emish
Тебе лучше уточнить свой ответ. Coding Mash
0

SELECT top 20 application_id, [name], location_id
FROM apps
WHERE (@lid > 0 AND location_id IN (@lid)) OR @lid <= 0
Был вопрос об обновлении, где сейчас CSV ... gbn
Хотя @lid был INT? Cade Roux
Как будет работать «location_id IN (@lid)» без динамического SQL? gbn
0

DECLARE @lid SMALLINT

SET @lid = 0

ВЫБЕРИТЕ топ-20 application_id, [name], location_id

Приложения от

ГДЕ ((@lid> 0 И location_id = @lid)

  OR (@lid = 0 AND location_id > @lid))

If @lid = 0, тогда он вернет ВСЕ строки. IF @lid имеет конкретное значение, возвращается только строка для этого значения @lid.

1

убрать пробелы и вставить значения во временную таблицу. Затем вы можете присоединить свой запрос к временной таблице.

Это фрагмент кода T-SQL, который разбивает список через запятую и вставляет элементы во временную таблицу. Заполнив таблицу, вы можете присоединиться к ней.

-- This bit splits up a comma separated list of key columns
-- and inserts them in order into a table. 
--
if object_id ('tempdb..#KeyCols') is not null
    drop table #KeyCols

create table #KeyCols (
      ,KeyCol           nvarchar (100)
)

set @comma_pos = 0
set @len = len(@KeyCols)
while @len > 0 begin
    set @comma_pos = charindex(',', @KeyCols)
    if @comma_pos = 0 begin
        set @KeyCol = @KeyCols
        set @KeyCols = ''
    end else begin
        set @KeyCol = left (@KeyCols, @comma_pos - 1)
        set @KeyCols = substring(@KeyCols, 
                                 @comma_pos + 1, 
                                 len (@KeyCols) - @comma_pos)
    end
    insert #KeyCols (KeyCol)
    values (@KeyCol)
    set @len = len (@KeyCols)
end
Не особенно - если я правильно помню, sproc, который изначально возник из заявленной жизни на SQL Server 2000. Вы можете также легко использовать переменную таблицы в современных версиях SQL Server. ConcernedOfTunbridgeWells
Хорошо, приятно знать. Я предпочитаю альтернативу в памяти. CRice
+ 1, полезная техника. Есть ли причина не использовать только временную таблицу в памяти? CRice
0

здесь все получается.

SELECT top 20 application_id, [name], location_id FROM apps
WHERE
  (@lid > 0 and @lid = location_id)
or
  (isnull(@lid, 0) <= 0 and 1=1)
0

с (о троичном операторе, но, как вы можете видеть, для этого вам не нужно ничего похожего на троичный оператор):

SELECT top 20 application_id, [name], location_id 
FROM apps
WHERE @lid = 0 OR location_id IN (@lid)

Но это было до того, как мы узнали, что @lid является varchar и может содержать разные значения, разделенные запятыми.

Ну, это (о CSV) еще один вопрос, который был задан здесь ранее:

Передача списка "in" через хранимую процедуру
Хранимая процедура T-SQL, которая принимает несколько значений Id

Как будет работать «location_id IN (@lid)» без динамического SQL? gbn
@ gbn: это не имеет никакого отношения к рассматриваемому вопросу, касающемуся троичного оператора (который также не является тем, что здесь необходимо, но это опять-таки еще одно препятствие; -). fretje

Похожие вопросы