查询数据库单刻度

Question

我想用数据库单拨号写这个查询：

1. 列出至少99%由岛屿组成的国家。
2. 结果必须包括国家名称、国家面积和岛屿总面积(提示:注意为联合王国获取的值​，因为它可能表示查询中的错误)。
3. 编写查询的两个版本。

我写了这个查询：

select c.name, c.area, ai.sum_area
from country as c 
inner join 
    (select sum(i.area) as sum_area, gi.country 
     from island i 
     inner join geo_island gi on (i.name = gi.island) 
     group by gi.country) as ai 
on (c.code = ai.country)
where (c.area * 0.99) <= ai.sum_area;

但结果是错误的！在一些国家，金额比国家的岛屿面积还要大！​我怎么纠正它？以下是来自mondial数据库的文档：http://www.dbis.informatik.uni-goettingen.de/Mondial/和Referential Dependencies of the Mondial Database

Country - area Country - Sum area islands    

"Guernsey";194;194    
"Iceland";103000;102829    
"Ireland";70280;84421    
"Jersey";117;117    
"Isle of Man";588;588    
"United Kingdom";244820;2622428.6    
"Bahrain";620;620    
"Brunei";5770;743122   
"Malaysia";329750;1486335    
"Christmas Island";135;135    
"Cyprus";9251;9251    
"Indonesia";1919440;10333702    
"Timor-Leste";15007;33850    
"Papua New Guinea";461690;794800    
"Japan";377835;8247391    
"Philippines";299764;1421325    
"Singapore";632.6;632.6    
"Sri Lanka";65610;65610    
"Antigua and Barbuda";442;442    
"Aruba";193;193    
"Barbados";430;430    
"Bermuda";53.3;53.3    
"Cuba";110860;1481284    
"Dominica";746;746    
"Dominican Republic";48730;76192    
"Haiti";27750;76192    
"Greenland";2175600;2175600    
"Grenada";344;344    
"Jamaica";10991;10991    
"Martinique";1128;1128    
"Montserrat";102;102    
"Curacao";444;444    
"Sint Maarten";34;87    
"Saint Martin";54;87    
"Saint Barthelemy";21;21    
"Puerto Rico";8870;8870    
"Saint Lucia";620;616    
"Trinidad and Tobago";5130;5128    
"Guam";541.3;541    
"Nauru";21;21
"New Zealand";268680;266354    
"Niue";260;260    
"Samoa";2860;2832    
"Madagascar";587041;587041    
"Mayotte";374;374 
"Reunion";2510;2510    
"Sao Tome and Principe";1001;1001

Answer 1

嗨，您可以试试这个查询。

SELECT c.name. c.area, x.sum_area
FROM country c 
INNER JOIN 
(SELECT gi.country,  SUM(i.area) as sum_area
FROM geo_island gi
INNER JOIN island i ON
gi.island = i.name
GROUP BY gi.country) x
ON c.name = x.country
WHERE (c.area * 0.99) <= x.sum_area;