在 SQL“IN"中使用元组条款 | 中使用元组条款

本文介绍了在 SQL“IN"中使用元组条款的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个包含 group_id 和 group_type 字段的表，我想从表中查询具有任何元组(group id、group type)的所有记录元组列表.例如，我希望能够执行以下操作:

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:

SELECT *
FROM mytable
WHERE (group_id, group_type) IN (("1234-567", 2), ("4321-765", 3), ("1111-222", 5))

一个非常相似的问题已经在:在 sql in 子句中使用元组，但那里提出的解决方案假定元组列表是从另一个表中获取的.这在我的情况下不起作用，因为元组值是硬编码的.

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.

一种解决方案是使用字符串连接:

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.

有什么建议吗?

推荐答案

这是一个过时的答案，虽然它是 2011 年被接受的答案，但获得更多赞成票的其他答案反映了最近的方法.

this is a dated answer, although it was the accepted answer in 2011, other answers with more upvotes reflect more recent approaches.

为什么不构造 OR 语句?

Why not construct the OR statements?

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)

当然，它看起来不像你的概念示例那么漂亮和整洁，但它会完成这项工作(如果你 IN 和元组确实存在，它会以完全相同的方式实现它最有可能的封面.

Granted, it doesn't look as nice and neat as your concept example but it will do the job (and if you IN with tuples did exist, it would implement it exactly the same way under the covers most likely.

这篇关于在 SQL“IN"中使用元组条款的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！