MySQL SQL 将一个查询的结果用于另一个查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46988538/
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
SQL Use Result from one Query for another Query
提问by curlie
This is an excerpt from one table:
这是一张表的摘录：
| id | type | other_id | def_id | ref_def_id|
| 1 | int | NULL | 5 | NULL |
| 2 | string | NULL | 5 | NULL |
| 3 | int | NULL | 5 | NULL |
| 20 | ref | 3 | NULL | 5 |
| 21 | ref | 4 | NULL | 5 |
| 22 | ref | 5 | NULL | 5 |
What I want is to find entries with type ref. Then I would for example have this one entry in my result:
我想要的是找到类型为 ref 的条目。然后我会在我的结果中包含这个条目：
| 22 | ref | 5 | NULL | 5 |
The problem I am facing is that I now want to combine this entry with other entries of the same table where def_id = 5.
我面临的问题是，我现在想将此条目与 def_id = 5 的同一表的其他条目结合起来。
So I would get all entries with def_id = 5 for this specific ref type as result. I somehow need the output from my first query, check what the ref_def_id is and then make another query for this id.
所以我会得到这个特定引用类型的 def_id = 5 的所有条目作为结果。我不知何故需要我的第一个查询的输出，检查 ref_def_id 是什么，然后对该 id 进行另一个查询。
I really have problems to understand how to proceed. Any input is much appreciated.
我真的很难理解如何继续。任何输入都非常感谢。
回答by Alberto Martinez
If I understand correctly you need to find rows with a typeof 'ref' and then use the values in their ref_def_idcolumns to get the rows with the same values in def_id. In that case you need to use a subquery for getting the rows with 'ref' type and combine it using either INor EXISTS:
如果我理解正确，您需要找到带有type'ref' 的行，然后使用其ref_def_id列中的值来获取def_id. 在这种情况下，您需要使用子查询来获取具有 'ref' 类型的行并使用INor组合它EXISTS：
select *
from YourTable
where def_id in (select ref_def_id from YourTable where type='ref');
select *
from YourTable
where exists (select * from YourTable yt
where yt.ref_def_id=YourTable.def_id and yt.type='ref')
Both queries are equivalent, INis easier to understand at first sight but EXISTSallow more complex conditions (for example you can use more than one column for combining with the subquery).
这两个查询是等价的，IN乍一看更容易理解，但EXISTS允许更复杂的条件（例如，您可以使用多个列与子查询组合）。
Edit:since you comment that you need also the idfrom the 'ref' rows then you need to use a subquery:
编辑：因为您评论说您还需要id来自'ref'行的，那么您需要使用子查询：
select source_id, YourTable.*
from YourTable
join (select id as source_id, ref_def_id
from YourTable
where type='ref')
as refs on refs.ref_def_id=YourTable.def_id
order by source_id, id;
With this for each 'ref' row you would get all the rows with the associated ref_id.
对于每个“参考”行，您将获得所有带有关联ref_id.
回答by Andre
What you are looking for is a subquery or even better a join operation.
您正在寻找的是子查询，甚至更好的连接操作。
Have a look here: http://www.mysqltutorial.org/mysql-left-join.aspx
看看这里：http: //www.mysqltutorial.org/mysql-left-join.aspx
Joins / the left join allows you to combine rows of tables within one query on a given condition. The condition could be id = 5 for your purpose.
联接/左联接允许您根据给定条件在一个查询中组合表行。出于您的目的，条件可能是 id = 5。
回答by Gordon Linoff
You would seem to want aggregation:
你似乎想要聚合：
select max(id) as id, type, max(other_id) as other_id,
max(def_id) as def_id, ref_def_id
from t
where type = 'ref'
group by type, ref_def_id
