mysql - 通过附加新列加入两个不同的 MySQL 结果查询

Question

我将不胜感激帮助找到一种方法如何使用 WHERE 和 IN 作为要求来组合/加入两个简单的 SQL 查询。

简单地说，我想要这个查询的结果:

SELECT Nationality, MAX(ActorName) as Oldest,
FROM ACTOR 
WHERE BirthDate IN
(SELECT MIN(BirthDate) as MinBday FROM ACTOR GROUP BY Nationality)
GROUP BY Nationality

结果是:

+------------+--------------+
| nationality| oldest       |
+------------+--------------+
| american   | brad pitt    |
+------------+--------------+
| british    | pierce bro   |
+------------+--------------+
| italian    | monica bellu |
+------------+--------------+

与此查询的结果结合:

SELECT Nationality, MAX(ActorName) as Oldest,
FROM ACTOR 
WHERE BirthDate IN
(SELECT MAX(BirthDate) as MinBday FROM ACTOR GROUP BY Nationality)
GROUP BY Nationality

结果是:

+------------+--------------+
| nationality| youngest     |
+------------+--------------+
| american   | angelina j   |
+------------+--------------+
| british    | jason stat   |
+------------+--------------+
| italian    | paul gia     |
+------------+--------------+

为了得到如下结果表:

+------------+--------------+--------------+
|nationality | oldest       | youngest     |
+------------+--------------+--------------+
| american   | brad pitt    | angelina j   |
+------------+--------------+--------------+
| british    | pierce bro   | jason stat   |
+------------+--------------+--------------+
| italian    | monica bellu | paul gia     |
+------------+--------------+--------------+

注意:这两个查询之间的唯一区别是 MIN(birthdate) 表示最大的生日，MAX(birthdate) 表示最小的生日。

Answer 1

可以遵循一个非常丑陋的sql，不是最有效的，只是一种方法。

SELECT
    t1.Nationality, t1.Oldest, t2.youngest
FROM (
    SELECT Nationality, MAX(ActorName) as Oldest -- ,
    FROM ACTOR 
    WHERE BirthDate IN
    (SELECT MIN(BirthDate) as MinBday FROM ACTOR GROUP BY Nationality)
    GROUP BY Nationality
) t1
JOIN (
    SELECT Nationality, MAX(ActorName) as youngest
    FROM ACTOR 
    WHERE BirthDate IN
    (SELECT MAX(BirthDate) as MaxBday FROM ACTOR GROUP BY Nationality)
    GROUP BY Nationality
) t2 ON t1.Nationality = t2.Nationality

或者试试这个(未测试):

SELECT 
    Nationality, 
    MAX(CASE WHEN BirthDate = t1.MinBday THEN ActorName ELSE NULL END) as Oldest,
    MAX(CASE WHEN BirthDate = t1.MaxBday THEN ActorName ELSE NULL END) as youngest
FROM ACTOR
JOIN (
    SELECT Nationality, MIN(BirthDate) as MinBday, MAX(BirthDate) as MaxBday
    FROM ACTOR GROUP BY Nationality) t
ON ACTOR.Nationality = t.Nationality
GROUP BY Nationality