连接两个不同的MySQL结果查询通过追加新的列

问题描述：

我似乎无法找到一种方法如何组合/加入的，因为我在何处及以要求两个简单的SQL结果的查询。连接两个不同的MySQL结果查询通过追加新的列

简单地说，我想这个查询的结果是：

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

导致此：

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| oldest  | 
+------------+--------------+ 
| american | brad pitt | 
+------------+--------------+ 
| british | pierce bro | 
+------------+--------------+ 
| italian | monica bellu | 
+------------+--------------+

与此查询的结果可以加入这会导致这样的：

+------------+--------------+ 
| 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  | 
+------------+--------------+--------------+

注：2个查询之间的唯一区别是MIN（出生日期），用于最古老的出生日期，和MAX（出生日期），用于最小的生日。

答

一个非常丑陋的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

希望的是直接的（即使丑陋）答案像这样的工作，但我忘了我在微软Access这样做的，有人原因查询这样总是结了：'语法错误在FROM clause' – jmigdelacruz

@jmigdelacruz更新我的回答，只是多了一个逗号之前'from'。 – Blank

谢谢@forward！第一个查询效果很好。只是改变了，'加入'到'左连接'，并从'最旧 - '中删除' - ，' – jmigdelacruz

答

你可以得到的生日，可与直接加入像

SELECT a.Nationality, MAX(a.ActorName) as Oldest, 
FROM ACTOR a 
JOIN (SELECT Nationality, 
MIN(BirthDate) as MinBday, 
MAX(BirthDate) as MaxBday 
    FROM ACTOR 
    GROUP BY Nationality) xxx 
ON a.Nationality = xxx.Nationality 
AND (a.BirthDate = xxx.MinBday OR a.BirthDate = xxx.MaxBday) 
GROUP BY Nationality;

连接两个不同的MySQL结果查询通过追加新的列

相关推荐