SQL 从一个表中选择另一个匹配条件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5446778/
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 from one table matching criteria in another?
提问by Chris Nava
I'd really appreciate some help with an SQL query across tables. I realise this sort of thing is asked constantly, but I can't find a similar enough question to make sense of the answers.
我真的很感激跨表的 SQL 查询的一些帮助。我意识到这种事情经常被问到，但我找不到足够类似的问题来理解答案。
I want to select rows from table_Athat have a corresponding tag in table_B.
So, for example, " select rows from table_awhich are tagged 'chair' " would return table_C.
我想从中选择table_A具有相应标记的行table_B。
因此，例如，“选择table_a标记为‘椅子’的行”将返回table_C。
Also, idis a unique in table_a, and not in table_b.
此外，在 中id是唯一的table_a，而不是在 中table_b。
table_A: table_B: table_C:
id object id tag id object
1 lamp 1 furniture 3 stool
2 table 2 furniture 4 bench
3 stool 3 furniture
4 bench 4 furniture
4 chair
3 chair
Alternatively, is there a better way to organise the data?
或者，有没有更好的方法来组织数据？
回答by Chris Nava
The simplest solution would be a correlated sub select:
最简单的解决方案是相关的子选择：
select
A.*
from
table_A A
where
A.id in (
select B.id from table_B B where B.tag = 'chair'
)
Alternatively you could jointhe tables and filter the rows you want:
或者，您可以加入表格并过滤所需的行：
select
A.*
from
table_A A
inner join table_B B
on A.id = B.id
where
B.tag = 'chair'
You should profile both and see which is faster on your dataset.
您应该对两者进行分析，看看哪个在您的数据集上更快。
回答by rjschnorenberg
You should make tags their own table with a linking table.
您应该使用链接表使标签成为自己的表。
items:
id object
1 lamp
2 table
3 stool
4 bench
tags:
id tag
1 furniture
2 chair
items_tags:
item_id tag_id
1 1
2 1
3 1
4 1
3 2
4 2
回答by RichardTheKiwi
select a.id, a.object
from table_A a
inner join table_B b on a.id=b.id
where b.tag = 'chair';
回答by KohanJ
I have a similar problem (at least I think it is similar). In one of the replies here the solution is as follows:
我有一个类似的问题（至少我认为它是相似的）。在此处的其中一个答复中，解决方案如下：
select
A.*
from
table_A A
inner join table_B B
on A.id = B.id
where
B.tag = 'chair'
That WHERE clause I would like to be:
我想成为的那个 WHERE 子句：
WHERE B.tag = A.<col_name>
or, in my specific case:
或者，在我的具体情况下：
WHERE B.val BETWEEN A.val1 AND A.val2
More detailed:
更详细：
Table A carries status information of a fleet of equipment. Each status record carries with it a start and stop time of that status. Table B carries regularly recorded, timestamped data about the equipment, which I want to extract for the duration of the period indicated in table A.
表 A 载有一组设备的状态信息。每个状态记录都带有该状态的开始和停止时间。表 B 包含有关设备的定期记录的带时间戳的数据，我想在表 A 所示的时间段内提取这些数据。
