SQL：在单个 SELECT 语句中使用多个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1031505/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: Use multiple values in single SELECT statement
提问by Mike B
I'm using a SELECT statement in T-SQL on a table similar to this:
我在 T-SQL 中对类似于此的表使用 SELECT 语句：
SELECT DISTINCT name, location_id, application_id FROM apps
WHERE ((application_id is null) or (application_id = '4'))
AND ((location_id is null) or (location_id = '3'))
This seems to work fine when searching for one application_id or one location_id, but what if I want to run the statement for multiple locations? I want to return all results for an unknown amount of location_id's and application_id's. For example, if I wanted to search for someone at the location_id's 2, 3, 4, 5 but with only one application_id. How would I do this?
这在搜索一个 application_id 或一个 location_id 时似乎工作正常，但是如果我想为多个位置运行语句怎么办？我想返回未知数量的 location_id 和 application_id 的所有结果。例如，如果我想在 location_id 的 2、3、4、5 处搜索某人，但只有一个 application_id。我该怎么做？
Thank you in advance!
先感谢您！
EDIT:I'm an idiot! I made it sound easy without giving you all the full details. All of these values are given from inside a table. The user will have to choose the id's from a column in the table instead of inserting them. After doing a bit of research on this problem I came up with a pagethat seemed to tout a viable solution.
编辑：我是个白痴！我让它听起来很简单，但没有提供所有完整的细节。所有这些值都是从表中给出的。用户必须从表中的列中选择 ID，而不是插入它们。在对这个问题进行了一些研究后，我想出了一个似乎宣传可行解决方案的页面。
CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX))
RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int
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
RETURN
END
Can anyone help me perfect this to meet my needs? Sorry if my question isn't very clear, as I'm struggling to get to grips with it myself.
谁能帮我完善这个以满足我的需求？对不起，如果我的问题不是很清楚，因为我自己正在努力解决它。
EDIT 2:After doing a bit more reading on the subject it seems that I need a stored procedure to do this. The code up the top seems to be what I need, but I'm having trouble tailoring it to my needs. The table structure is as follows:
编辑 2：在对该主题进行了更多阅读之后，似乎我需要一个存储过程来执行此操作。顶部的代码似乎是我需要的，但我无法根据我的需要定制它。表结构如下：
application_id name location_id
------------------------------------------------------
1 Joe Blogs 34
2 John Smith 55
According to the article I've just linked to:
根据我刚刚链接到的文章：
"The correct way of handling the situation is to use a function that unpacks the string into a table. Here is a very simple such function:"
“处理这种情况的正确方法是使用一个函数将字符串解包到一个表中。这是一个非常简单的函数：”
So, it seems that I need to unpack these values into a string and pass them through using this stored procedure. Any idea on how I can get this to work?
所以，似乎我需要将这些值解包成一个字符串并使用这个存储过程传递它们。关于如何让它发挥作用的任何想法？
EDIT 3:I've managed to solve it using charindex() and convert(), whilst setting them at the top. Thank you for all your help and I apologise again for being a pain.
编辑 3：我设法使用 charindex() 和 convert() 解决它，同时将它们设置在顶部。感谢您的所有帮助，我再次为造成痛苦而道歉。
回答by Joel Goodwin
Use IN as follows:
按如下方式使用 IN：
location_id IN ('2', '3', '4', '5')
回答by Christian Specht
I'm not sure if I understood the "EDIT" part of your question right, but do you mean something like this?
我不确定我是否理解你问题的“编辑”部分，但你的意思是这样的吗？
SELECT DISTINCT name, location_id, application_id
FROM apps
WHERE location_id IN
(
SELECT id FROM other_table
)
EDIT:
Now that I read your second edit, I'm still not sure if I understand what you REALLY want to do.
编辑：
现在我读了你的第二个编辑，我仍然不确定我是否理解你真正想要做什么。
Of course, we can help you with your stored procedure, but there's one thing which is not quite clear to me:
当然，我们可以帮助你处理你的存储过程，但有一件事我不太清楚：
Do you want us to help you create the SP?
Or do you really want to do something else, but think you must do it by creating a SP because the link said so?
您是否希望我们帮助您创建 SP？
或者您真的想做其他事情，但认为您必须通过创建 SP 来完成，因为链接是这样说的？
Can you specify how the expected result looks like?
你能指定预期结果的样子吗？
回答by Steve Weet
You can use an IN clause as follows :-
您可以使用 IN 子句如下：-
SELECT DISTINCT name, location_id, application_id FROM apps
WHERE ((application_id is null) or (application_id = '4'))
AND ((location_id is null) or (location_id in ('2', '3', '4', '5')))
