mysql - 基于特定 SQL 组的列表

Question

如何从下表中列出表面积大于同一地区所有其他国家的所有国家的名称:

+----------------------------------------------+---------------------------+-------------+
| name                                         | region                    | surfacearea |
+----------------------------------------------+---------------------------+-------------+
| Aruba                                        | Caribbean                 |      193.00 |
| Afghanistan                                  | Southern and Central Asia |   652090.00 |
| Angola                                       | Central Africa            |  1246700.00 |
| Anguilla                                     | Caribbean                 |       96.00 |
| Albania                                      | Southern Europe           |    28748.00 |
| Andorra                                      | Southern Europe           |      468.00 |
| Netherlands Antilles                         | Caribbean                 |      800.00 |

到目前为止，我已经获得了这段代码，但它没有列出国家？这段代码正确吗？

select region, max(surfacearea) as maxArea 
from country 
group by region; 

Answer 1

可能是您可以对临时表使用内部联接

 select name from 
 country  as a
 inner  join      
 ( select region, max(surfacearea) as maxarea 
 from country 
 group by region ) as t    on a.region = t.region    
 where a.surfacearea =  t.maxarea;