根据唯一对列表从数据库中选择 [英] Selecting from a Database based on a list of unique pairs

问题描述

例如，如果我得到下表

Id        Key             Value

 1          A             Alpha
 2          B             Alpha
 3          A             Charlie

然后我接受了输入{{ A，查理）和（B，阿尔法）}，我要求返回所有我希望它返回2和3但不返回1的ID。

And I took the input {(A, Charlie) and (B, Alpha)} and I asked to return all the IDs I would want it to return 2 and 3 but NOT 1.

什么是最好的方法吗？我可以将它们全部组合成一个查询（以提高速度），还是必须对收到的每个值对都运行重复查询。

What is the best way to do this? Can I combine it all into one query, (for speed) or would I have to run a repeat query for each value pair I received.

推荐答案

我认为Postgresql是最优雅的解决方案：

I think Postgresql has the most elegant solution:

SELECT  *
FROM    T
WHERE   ("Key", "Value") IN (('B', 'Alpha'), ('A', 'Charlie'));

SQL小提琴示例

在SQL-SERVER 2008及更高版本中，您可以使用 VALUES 建立元组：

In SQL-SERVER 2008 and onward you can use VALUES to build your tuples:

SELECT  T.*
FROM    T
        INNER JOIN
        (   VALUES
                ('B', 'Alpha'),
                ('A', 'Charlie')
        ) v (Key, Value)
            ON v.Key = T.Key
            AND v.Value = T.Value

SQL小提琴示例

或对于一个过程，您可以创建一个键值对类型并将其作为参数传递：

Or for a procedure you could create a key-value pair type and pass this as a parameter:

CREATE TYPE KeyValuePair AS TABLE ([Key] VARCHAR(1), [Value] VARCHAR(7));

DECLARE @T AS KeyValuePair
INSERT @T 
VALUES
    ('B', 'Alpha'),
    ('A', 'Charlie')


SELECT  T.*
FROM    T
        INNER JOIN @T v
            ON v.[Key] = T.[Key]
            AND v.Value = T.Value;

SQL小提琴示例

对于MySQL，我认为您可能只需要使用 AND进行构建/ OR

For MySQL I think you may have to just build this using AND/OR

SELECT  *
FROM    T
WHERE   (`Key` = 'A' AND `Value` = 'Charlie')
OR      (`Key` = 'B' AND `Value` = 'Alpha')

关于SQL Fiddle的示例

我对其他DBMS的了解有限，因此，如果不是上述之一，对不起，我将无济于事。

My Knowledge of other DBMS is limited, so if it is not one of the above sorry I can't be of more help.

编辑（借助 a_horse_with_no_name ）

EDIT (With the help of a_horse_with_no_name)

PostgreSQL语法也可用于 Oracle （我认为是DB2）

The PostgreSQL syntax also works for Oracle (and I think DB2)

这篇关于根据唯一对列表从数据库中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！