问题描述
问题本身很简单，但我不能找出一个解决方案，它在一个查询，这里是我的抽象的问题，以允许一个更简单的解释：
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 |
+----+-----+
这篇关于在一组两种可能性中选择唯一的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！