在 SQL“IN”子句中使用元组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8006901/
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
Using tuples in SQL "IN" clause
提问by Rafid
I have a table containing the fields group_id and group_type and I want to query the table for all the records having any tuple (group id, group type) from a list of tuples. For example, I want to be able to do something like:
我有一个包含字段 group_id 和 group_type 的表，我想从元组列表中查询具有任何元组（group id，group type）的所有记录的表。例如，我希望能够执行以下操作：
SELECT *
FROM mytable
WHERE (group_id, group_type) IN (("1234-567", 2), ("4321-765", 3), ("1111-222", 5))
A very similar question is already asked at: using tuples in sql in clause, but the solution proposed there presumes the tuple list is to be fetched from another table. This doesn't work in my case is the tuple values are hard coded.
已经提出了一个非常相似的问题：在 sql in 子句中使用元组，但那里提出的解决方案假定元组列表是从另一个表中获取的。这在我的情况下不起作用，因为元组值是硬编码的。
One solution is to use string concatenation:
一种解决方案是使用字符串连接：
SELECT *
FROM mytable
WHERE group_id + STR(group_type, 1) IN ("1234-5672", "4321-7653", "1111-2225")
But the problem is that the table is quite big and doing a string concatenation and conversion for each record would be very expensive.
但问题是表非常大，对每条记录进行字符串连接和转换会非常昂贵。
Any suggestion?
有什么建议吗？
采纳答案by Code Magician
Why not construct the OR statements?
为什么不构造 OR 语句？
SELECT *
FROM mytable
WHERE (group_id = '1234-567' and group_type = 2)
OR (group_id = '4321-765' and group_type = 3)
OR (group_id = '1111-222' and group_type = 5)
Granted, it doesn't look as nice and neat as your concept example but it will do the job (and if you INwith tuples did exist, it would implement it exactly the same way under the covers most likely.
当然，它看起来不像你的概念示例那么漂亮和整洁，但它会完成这项工作（如果你IN确实存在元组，它很可能会以完全相同的方式在封面下实现它。
回答by onedaywhen
Given a very minor tweak (replace double quotes with single and add the VALUESkeyword), your proposed syntax is valid Standard SQL-92 syntax i.e.
给定一个非常小的调整（用单引号替换双引号并添加VALUES关键字），您建议的语法是有效的标准 SQL-92 语法，即
SELECT *
FROM mytable
WHERE (group_id, group_type) IN (
VALUES ('1234-567', 2),
('4321-765', 3),
('1111-222', 5)
);
Sadly, MSFT have not added it to SQL Server and consider it an 'unplanned' feature.
遗憾的是，MSFT 尚未将其添加到 SQL Server 中，并将其视为“计划外”功能。
FWIW PostgreSQL and Sqlite are examples of SQL products that support this syntax.
FWIW PostgreSQL 和 Sqlite 是支持此语法的 SQL 产品示例。
回答by Mikael Eriksson
In SQL Server 2008 you can do like this:
在 SQL Server 2008 中，您可以这样做：
select *
from mytable as T
where exists (select *
from (values ('1234-567', 2),
('4321-765', 3),
('1111-222', 5)) as V(group_id, group_type)
where T.group_id = V.group_id and
T.group_type = V.group_type
)
回答by Damien_The_Unbeliever
You can use a common table expression to pretend that these tuples are in another table:
您可以使用公共表表达式来假装这些元组在另一个表中：
;WITH Tuples as (
select '1234-567' as group_id, 2 as group_type union all
select '4321-765', 3 union all
select '1111-222', 5
)
SELECT * /* TODO - Pick appropriate columns */
from mytable m where exists (
select * from Tuples t
where m.group_id = t.group_id and m.group_type = t.group_type)
回答by satnhak
Using that solution, this should work:
使用该解决方案，这应该有效：
SELECT *
FROM mytable m
WHERE EXISTS (
SELECT * FROM (
SELECT "1234-567" group_id, 2 group_type UNION ALL
SELECT "4321-765", 3 UNION ALL
SELECT "1111-222", 5) [t]
WHERE m.group_id = t.group_id AND m.group_type = t.group_type)
BTW, you should probably use a CTEto create that inner table.
顺便说一句，您可能应该使用CTE来创建该内部表。
回答by Lee
Here is another tuple solution using a join:
这是另一个使用连接的元组解决方案：
SELECT
*
FROM mytable m
JOIN
(
SELECT "1234-567" group_id, 2 group_type
UNION ALL SELECT "4321-765", 3
UNION ALL SELECT "1111-222", 5
) [t]
ON m.group_id = t.group_id
AND m.group_type = t.group_type
回答by Dawid
I had a similar problem but my tuple collection was dynamic - it was sent over to the SQL Server in a query parameter. I came up with the following solution:
我有一个类似的问题，但我的元组集合是动态的 - 它在查询参数中发送到 SQL Server。我想出了以下解决方案：
Pass a tuple as an XML:
DECLARE @tuplesXml xml = '<tuples><tuple group-id="1234-567" group-type="2"/><tuple group-id="4321-765" group-type="3"/></tuples>';Inner join the table that you want to filter with the XML nodes:
SELECT t.* FROM mytable t INNER JOIN @tuplesXml.nodes('/tuples/tuple') AS tuple(col) ON tuple.col.value('./@group-id', 'varchar(255)') = t.group_id AND tuple.col.value('./@group-type', 'integer') = t.group_type
将元组作为 XML 传递：
DECLARE @tuplesXml xml = '<tuples><tuple group-id="1234-567" group-type="2"/><tuple group-id="4321-765" group-type="3"/></tuples>';使用 XML 节点对要过滤的表进行内部联接：
SELECT t.* FROM mytable t INNER JOIN @tuplesXml.nodes('/tuples/tuple') AS tuple(col) ON tuple.col.value('./@group-id', 'varchar(255)') = t.group_id AND tuple.col.value('./@group-type', 'integer') = t.group_type
It seems to work fine in my situation which is a bit more complex than the one described in the question.
在我的情况下它似乎工作得很好，这比问题中描述的要复杂一些。
Keep in mind that it is necessary to use t.*instead of *and the table returned from nodesmethod needs to be aliased (it's tuple(col)in this case).
请记住，有必要使用t.*而不是，*并且从nodes方法返回的表需要别名（tuple(col)在这种情况下）。
回答by Ashish Kumar
select * from table_name where 1=1 and (column_a, column_b) in ((28,1),(25,1))
