mysql - JOIN 两个查询结果(具有 WHERE 和 IN 子句 + 子查询)

Question

尝试重现以下问题(不包括 IN 子句和子查询):

/* this is query 1 */    
Select A.column_1,B.keyValue from table1 as A, table2 as B where
A.someColumn = B.someColumn and B.someotherColumn = 10

/* query 1 gives */
column1 | keyValue
_________________

data-A1 | key-1 
data-A2 | key-2
data-A3 | key-3

/* this is query 2 */
Select AVG(ratings) as ratings, C.keyValue from table3 as C, 
table4 as D where C.someColumn = D.someColumn and D.someotherColumn = 'abc'

/* query 2 gives */
ratings | keyValue
_________________

rating-1 | key-1 
rating-2 | key-2
rating-3 | key-3

/* this is the desired result */

column1 | ratings | keyValue
_________________

data-A1 | rating-1 | key-1 
data-A2 | rating-2 | key-2
data-A3 | rating-3 | key-3

我用谷歌搜索了一下，发现 mysql join 是解决方案

SELECT table1.id, table2.column1, table1.column2 FROM table1 
INNER JOIN table2 ON table1.id = table2.id;

但是这是一个非常基本的示例，仅涉及两个表，我的第一个查询实际上涉及 5 个表，第二个查询涉及 4 个表，其中包含多个 WHERE 和 IN 子句 + 子查询。我无法通过复杂的查询来实现这个 JOIN 逻辑。这是我尝试过的，但在“JOIN”关键字后给了我一个错误:

Select * from (Select A.column_1,B.keyValue from table1 as A, table2 as B 
where A.someColumn = B.someColumn and B.someotherColumn = 10) as first
JOIN
Select * from (Select AVG(ratings) as ratings, C.keyValue from table3 as C, 
table4 as D where C.someColumn = D.someColumn and D.someotherColumn = 'abc')
as second ON first.keyValue = second.keyValue;

如有任何帮助，我们将不胜感激。

Answer 1

我不知道您的 4 个表的结构，但根据您的 2 个查询，您可以执行以下操作:

select
   X.column_1,
   Y.ratings,
   X.key_value 
From
   (
      Select
         A.column_1,
         B.keyValue 
      from
         table1 as A,
         table2 as B 
      where
         A.someColumn = B.someColumn 
         and B.someotherColumn = 10 
   )
   X 
   INNER JOIN
      (
         Select
            AVG(ratings) as ratings,
            C.keyValue 
         from
            table3 as C,
            table4 as D 
         where
            C.someColumn = D.someColumn 
            and D.someotherColumn = 'abc' 
      )
      Y 
      on X.keyvalue = Y.keyvalue;

X 和 Y 称为派生表。

P.S:如果我们知道您的基础表的结构、示例数据以及您想要实现的目标，则可能会创建更好的查询。这是我根据信息能给您的最佳答案。