如何在逗号中使用逗号分隔的值列表作为过滤器？

I have a basic SQL query, starting with:

我有一个基本的SQL查询,从以下开始:

SELECT top 20 application_id, [name], location_id FROM apps

Now, I would like to finish it so that it does this (written in Pseudocode)

现在,我想完成它,以便它这样做(用伪代码编写)

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

As requested, here is an example

根据要求,这是一个例子

application_id             name               location_id
----------------------------------------------------------
1                          Joe Blogs          33
2                          Sam Smith          234
3                          Jeremy Carr        33

@locid is the results given by the user, for example '33, 234'

@locid是用户给出的结果,例如'33,234'

If @lid is empty then I'd like it to output all rows for location_id with name and application_id. Otherwise, I'd like it to output all rows in relation to the provided numbers in @lid (standing for location_id.

如果@lid为空,那么我希望它输出具有name和application_id的location_id的所有行。否则,我希望它输出与@lid中提供的数字相关的所有行(代表location_id。

So, if @lid is 0:

所以,如果@lid是0:

application_id             name               location_id
----------------------------------------------------------
1                          Joe Blogs          33
2                          Sam Smith          234
3                          Jeremy Carr        33

Otherwise, if @lid contains '33'

否则,如果@lid包含'33'

application_id             name               location_id
----------------------------------------------------------
1                          Joe Blogs          33
3                          Jeremy Carr        33

10 个解决方案

#1

Try using Case, which serves the purpose of an IIF or a ternary operator. Please check this link http://msdn.microsoft.com/en-us/library/ms181765.aspx

尝试使用Case,它用于IIF或三元运算符。请查看此链接http://msdn.microsoft.com/en-us/library/ms181765.aspx

cheers

#2

If @locid is a list eg "33, 234" etc, then no solution here will work. However, I guess these were posted before your update with this information.

如果@locid是一个列表,例如“33,234”等,那么这里没有解决方案。但是,我想这些是在您使用此信息进行更新之前发布的。

I assume that because you said this:

我假设因为你这样说:

@locid is the results given by the user, for example '33, 234'

@locid是用户给出的结果,例如'33,234'

You can not expand the variable directly so that location_in IN (33, 234). You are actually asking for location_id = '33, 234', which will fail with a CAST conversion, because of datatype precedence.

您不能直接扩展变量,以便location_in IN(33,234)。您实际上要求的是location_id = '33,234',由于数据类型优先,它将因CAST转换而失败。

You have to parse the list first into a table form for use in a JOIN/EXISTS construct. There are several options and Erland covers them all here: Arrays and Lists in SQL Server 2005

你必须先解析列表转换为表格形式使用加入/ EXISTS构建。有几个选项和厄兰涵盖他们都在这里:数组和列表在SQL Server 2005

#3

Assuming @locid & @lid are the same, I'm going to use @locid... The following would work. I've split it up to keep it looking good on SO.

假设@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
  )

#4

See this entry in my blog:

请参阅我的博客中的此条目:

IN with a comma separated list: SQL Server

IN用逗号分隔的列表:SQL Server

If your @lid is a comma-delimited list of integers, use this:

如果您的@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'

This is much more efficient than using OR constructs.

这比使用OR结构更有效。

#5

WHERE CHARINDEX(LocationId, @lid) > 1

#6

One way is to split the string on the commas, strip out the spaces and insert the values into a temporary table. Then you can join your query against the temp table.

一种方法是在逗号上拆分字符串,去掉空格并将值插入临时表。然后,您可以针对临时表加入查询。

This is a T-SQL code snippet that splits a comma-separated list and inserts the members into a temporary table. Once you've populated the table you can join against it.

这是一个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

#7

You don't really need the ternary operator for this:

你真的不需要三元运算符:

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

#8

The following should do the trick for you.

以下应该为你做的伎俩。

DECLARE @lid SMALLINT

SET @lid = 0

SELECT top 20 application_id, [name], location_id

SELECT top 20 application_id,[name],location_id

FROM apps

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

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

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

If @lid = 0 then it will return ALL rows. IF @lid has a particular value, only the row for that @lid value is returned.

如果@lid = 0则返回所有行。如果@lid具有特定值,则仅返回该@lid值的行。

#9

While it is not exactly a best practice to use 1=1, it does the trick here.

虽然使用1 = 1并不是最佳实践,但它可以解决这个问题。

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)

#10

You are asking different questions here. This is the answer to your original question (about the ternary operator, but as you can see, you don't need anything like a ternary operator for this):

你在这里问不同的问题。这是您原始问题的答案(关于三元运算符,但正如您所看到的,您不需要像三元运算符那样):

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

But that was before we knew that @lid was a varchar and could contain different comma separated values.

但那是在我们知道@lid是一个varchar并且可能包含不同的逗号分隔值之前。

Well, this (about the csv) is another question which has been asked here before:

好吧,这个(关于csv)是另一个问题,这个问题在此之前被提出过:

Passing an "in" list via stored procedure
T-SQL stored procedure that accepts multiple Id values

通过存储过程接收多个Id值的T-SQL存储过程传递“in”列表

#1