在同一个查询中使用查询结果? [英] Use result from query in same query?

问题描述

我有一个 MySQL 问题，我想知道答案是否在于使用连接.如果标题引起混淆或误导，我提前道歉.

I have a MySQL question, I'm wondering if the answers lies in using joins. I apologize in advance if the title is confusing or misleading.

我有 2 个表:团队、承包商

I have 2 tables: Teams, Contractors

TEAMS
|team_id | location|
--------------------
|    1   |  space1 |
--------------------
|    2   |  space2 |
--------------------
|    3   |  space3 |
--------------------

<小时>

CONTRACTORS
|cont_id | location| team_id|
-----------------------------
|    1   |  space1 |    1   |
-----------------------------
|    2   |  space1 |    0   |
-----------------------------
|    3   |  space3 |    3   |
-----------------------------
|    4   |  space3 |    3   |
-----------------------------
|    5   |  space3 |    0   |
-----------------------------

我想创建一个查询来查找位于特定位置的所有承包商.问题是，我只知道 team_id.(CONTRACTORS 表中的team_id = 0 表示承包商当前不属于团队).

I would like to create a query that finds all the contractors that are at a specific location. The issue is, I only know the team_id. (team_id = 0 in the CONTRACTORS table means the contractor is not currently part of a team).

例如，我得到了 team_id = 3. team_id = 3 的团队位于 space3.我想找到所有位于 space3 的承包商(在本例中为 cont_id = 3, 4, 5).

For example, I'm given the team_id = 3. The team with team_id = 3 is located at space3. I want to find all the contractors that are located at space3 (cont_id = 3, 4, 5 in this example).

有没有办法通过单个 MySQL 查询来实现这一点?或者我是否需要执行查询以获取团队的位置，然后进行第二次查询以找到该位置的所有承包商?

Is there a way to achieve this with a single MySQL query? Or do I need to perform a query to get the location of the team, and then a 2nd query to find all contractors at that location?

推荐答案

select location 
from CONTRACTORS 
inner join TEAMS on TEAMS.location = CONTRACTORS.location and TEAMS.team_id = 3

这篇关于在同一个查询中使用查询结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！