sql - MySQL:是否可以将 GROUP-BY 的结果加入两个 SELECT？

Question

我有两个单独的 SELECT 语句，它们都是分别 GROUP-BY 的，例如:

SELECT x, y, z FROM a GROUP BY x
SELECT x, n, o FROM b GROUP BY x

我很想将这两个SELECTs JOIN 在一起，合并它们的列，例如:

SELECT x as x1, y, z FROM a GROUP BY x 
LEFT JOIN (
  SELECT x as x2, n, o FROM b GROUP BY x)
ON x1=x2;

这可能吗？我问是因为 MySQL 在提示

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN SELECT x as x2

如果可能的话，我的语法有什么问题吗？

非常感谢!

Answer 1

这行得通

select * from (
    (select 1 a,2 b,3 c) t1 left join (select null a,2 b,5 c) t2 on (t1.b=t2.b)
);

或者，

select * from (
    (select 1 a,2 b,3 c) t1 left join (select null a,2 b,5 c) t2 using (b)
);

两者都导致

+---+---+---+------+---+---+
| a | b | c | a    | b | c |
+---+---+---+------+---+---+
| 1 | 2 | 3 | NULL | 2 | 5 |
+---+---+---+------+---+---+
1 row in set (0.00 sec)