MySQL 从表中选择行，其中具有相同 id 的另一个表中的行在另一列中具有特定值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9105427/
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
Select rows from a table where row in another table with same id has a particular value in another column
提问by Jonathon Oates
In MySQL:
在 MySQL 中：
If we have two tables:
如果我们有两个表：
comments
key | value
=================
1 | foo
2 | bar
3 | foobar
4 | barfoo
and:
和：
meta
comment_key | value
=========================
1 | 1
2 | 1
3 | 2
4 | 1
I want to get the comments from the commenttable that have a corresponding comment_keyin the metatable that have a specific value(the valuecolumn in the metatable).
我想从comment表格中获取评论，这些评论comment_key在meta表格中具有特定的value（表格中的value列meta）。
For example, I'd like to select all the rows from the commenttable that have a valueof 1in the metatable:
例如，我想选择所有从该行comment有一个表value中1的meta表：
I'd expect these results:
我希望这些结果：
key | value
=================
1 | foo
2 | bar
4 | barfoo
And if I were to select all the rows from the commenttable that have a valueof 2in the metatable:
而如果我选择所有从该行comment表中有一个value的2在meta表：
I'd expect this result:
我希望这个结果：
key | value
=================
3 | foobar
I really hope someone can help, thank you all in advance!
我真的希望有人可以提供帮助，提前谢谢大家！
I think I need to do a join? Any pointers would be great, and if at all possible, a short explanation so I can work out where I was going wrong -> so I'll know for next time!
我想我需要加入？任何指针都会很棒，如果可能的话，还有一个简短的解释，这样我就可以找出我哪里出错了 -> 这样我下次就知道了！
回答by ruakh
I actually wouldn't recommend a JOIN for this — or rather, I'd recommend a "semijoin", which is a relational-algebra concept not directly expressed in SQL. A semijoin is essentially a join where you want to retrieve records from only one table, but with the proviso that they have corresponding records in a different table.
我实际上不会为此推荐 JOIN - 或者更确切地说，我会推荐“ semijoin”，这是一个没有直接在 SQL 中表达的关系代数概念。半连接本质上是一种连接，您只想从一个表中检索记录，但条件是它们在不同的表中有相应的记录。
In SQL notation, this concept is expressed indirectly, by using an INclause, with a subquery:
在SQL符号，这个概念间接表达，通过使用一个IN子句，具有子查询：
SELECT key, value
FROM comments
WHERE key IN
( SELECT comment_key
FROM meta
WHERE value = 1
)
;
(MySQL will actually end up translating that back into a semijoin internally — essentially a sort of degenerate inner-join — but the INclause is the natural way to express it in raw SQL.)
（MySQL 实际上最终会在内部将其转换回半连接——本质上是一种退化的内连接——但该IN子句是在原始 SQL 中表达它的自然方式。）
回答by SnowCrash
You're looking for a plain, vanilla equi-join here.
您正在这里寻找一个普通的香草 equi-join。
SELECT `comment`.`key` AS `key`,
`comment`.`value` AS `value`
FROM `comments`
JOIN `meta`
ON `comments`.`key` = `meta`.`comment_key`
WHERE `meta`.`value` = 1;
I'm not really sure what sort of advice you're looking for here but you can read more about the topic (not MySQL specific) at Wikipedia's SQL JOIN page.
我不确定您在这里寻找什么样的建议，但您可以在Wikipedia 的 SQL JOIN 页面上阅读有关该主题的更多信息（不是特定于 MySQL 的）。
I'd recommend indexing on comment.keyand meta.comment_keywith both being PRIMARY KEY indexes assuming that you want there to only be 1 metarow per commentrow (PRIMARY KEYs are UNIQUE by definition). If you want to allow more than 1 metaper commentthen add a separate index idcolumn to metaand make that the PRIMARY KEY with comment_keyjust a b-tree index.
我建议在comment. key和meta。comment_key两个都是 PRIMARY KEY 索引，假设您希望meta每comment行只有 1行（PRIMARY KEY 根据定义是唯一的）。如果您想允许超过 1meta个，comment则添加一个单独的索引id列，meta并使其成为comment_key仅具有 b 树索引的 PRIMARY KEY 。
I'm also not sure how the performance of this will compare to the "semi-join" answer also listed but, to me, this is the simpler and more natural way to express the query; with only two tables, though, it shouldn't be too challenging for MySQL to optimize.
我也不确定它的性能与列出的“半连接”答案相比如何，但对我来说，这是表达查询的更简单、更自然的方式；然而，只有两个表，MySQL 优化应该不会太具有挑战性。
回答by Darius Miliauskas
I would use "INNER JOIN" in the following way:
我会以下列方式使用“INNER JOIN”：
SELECT comments.key, comments.value FROM comments
INNER JOIN meta ON comments.key=meta.comment_key WHERE meta.value = 1;
Cheers! ;-)
干杯! ;-)
