在一组两种可能性中选择唯一的行 [英] Selecting unique rows in a set of two possibilities

问题描述

问题本身很简单，但我不能找出一个解决方案，它在一个查询，这里是我的抽象的问题，以允许一个更简单的解释：

The problem itself is simple, but I can't figure out a solution that does it in one query, and here's my "abstraction" of the problem to allow for a simpler explanation:

我会让我的原始探索，但这里是一组示例数据和结果我期望：

，所以这里有一些示例数据，我用一个空白行将对分隔开。

Ok, so here's some sample data, i separated pairs by a blank line

-------------
| Key |  Col | (Together they from a Unique Pair)
--------------
|  1     Foo |
|  1     Bar |
|            |
|  2     Foo |
|            |
|  3     Bar |
|            |
|  4     Foo |
|  4     Bar |
--------------

我希望，运行查询一次后，需要能够在一个查询中选择此结果集：

And the result I would expect, after running the query once, it need to be able to select this result set in one query:

1 - Foo
2 - Foo
3 - Bar
4 - Foo

原始解释：

我有一个表，称为 TABLE 其中我有两列说 ID 和 NAME ，它们一起形成表的主键。现在我想选择 ID = 1 ，然后首先检查是否可以找到 NAME 值John，如果John不存在，它应该查找 NAME 是Bruce的行，但只有Bruce John存在或只有John存在当然。

I have a table, call it TABLE where I have a two columns say ID and NAME which together form the primary key of the table. Now I want to select something where ID=1 and then first checks if it can find a row where NAME has the value "John", if "John" does not exist it should look for a row where NAME is "Bruce" - but only return "John" if both "Bruce" and "John" exists or only "John" exists of course.

还要注意，它应该能够返回几行符合上述标准， /名称组合当然，上面的解释只是一个简单的真正的问题。

Also note that it should be able to return several rows per query that match the above criteria but with different ID/Name-combinations of course, and that the above explanation is just a simplification of the real problem.

我可以完全不知道自己的代码和思路，但我只是不能想出这一点。

I could be completely blinded by my own code and line of thought but I just can't figure this out.

推荐答案

这与你写的非常相似，但应该相当快，因为​​NOT EXISTS更高效，比NOT IN ...

This is fairly similar to what you wrote, but should be fairly speedy as NOT EXISTS is more efficient, in this case, than NOT IN...

mysql> select * from foo;
+----+-----+
| id | col |
+----+-----+
|  1 | Bar | 
|  1 | Foo | 
|  2 | Foo | 
|  3 | Bar | 
|  4 | Bar | 
|  4 | Foo | 
+----+-----+

SELECT id
     , col
  FROM foo f1 
 WHERE col = 'Foo' 
  OR ( col = 'Bar' AND NOT EXISTS( SELECT * 
                                     FROM foo f2
                                    WHERE f1.id  = f2.id 
                                      AND f2.col = 'Foo' 
                                 ) 
     ); 

+----+-----+
| id | col |
+----+-----+
|  1 | Foo | 
|  2 | Foo | 
|  3 | Bar | 
|  4 | Foo | 
+----+-----+

这篇关于在一组两种可能性中选择唯一的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！