MYSQL:根据联接表的排序选择行

Question

我有三张桌子：

country

Id  | code   |  name
-------------------------
1   | DE     |  Germany
2   | IT     |  Italy
3   | ES     |  Spain
4   | FR     |  France

货币

Id  | code     |   name          
-------------------------------
1   |  EUR     |   Euro            
2   |  USD     |   US Dollors      
3   |  CAD     |   Canadian Dollors 

country_currency

country_id    |  currency_id |  ranking
-------------------------------------------
1             |  1           |  5    
1             |  2           |  10      
1             |  3           |  15      
2             |  1           |  5      
3             |  1           |  10      
4             |  2           |  5      
4             |  3           |  10     

正如您在第三张表中所看到的，country_id => 1有三种不同排名的指定货币。换句话说，德国有三种指定货币(欧元、美元和加元)。因此，欧元应该显示与德国，因为它有5个排名，这是最低的。

对于意大利和西班牙，只分配了currency_id => 1 (欧元)。因此，欧元应该展示给意大利和西班牙。

对于法国，currency_id => 2和currency_id => 3 (美元和CAD)，但美元有最低的排名与法国行。因此，美元应该显示与法国。

预期结果

country         | currency
---------------------------
Germany         |  EUR                 
Italy           |  EUR                 
Spain           |  EUR                 
France          |  USD                 

我的查询

select country.name, currency.code from country_currency 
inner join country on country.id = currency_country.country_id 
inner join currency on currency.id = currency_country.currency_id
order by ranking asc
group by country_id

它坏了。有人能帮我修复这个查询吗？

SQLFIDDLE

Answer 1

您可以使用以下查询：

SELECT name AS country, 
      (SELECT c3.code
       FROM country_currency AS c2 
       INNER JOIN currency AS c3 ON c3.id = c2.currency_id
       WHERE c1.id = c2.country_id 
       ORDER BY ranking LIMIT 1) AS currency
FROM country AS c1;

上述查询利用单个相关子查询，得到每个国家最低排名的货币的code值。

注：如果多种货币对某一特定国家具有相同的最低排名，则任意选择其中一种货币。

这里的演示

Answer 2

http://sqlfiddle.com/#!9/2a099/7

SELECT c.name, curr.name
FROM country c
LEFT JOIN country_currency cc
ON cc.country_id = c.id
LEFT JOIN country_currency cc_min
ON cc_min.country_id = cc.country_id
  AND cc.ranking > cc_min.ranking
LEFT JOIN currency curr
ON curr.id = cc.currency_id
WHERE cc_min.ranking IS NULL;