SQL查找一个国家中使用最多的语言[重复]

inner join...on .

您的查询将无法工作,因为它将获得最大值(百分比),但会显示以下任何内容: language 对于给定 country .

SELECT co.Name, cl.language, cl.Percentage as percentage
from Country co 
inner join CountryLanguage cl
on co.Code = cl.CountryCode
where (cl.CountryCode,cl.Percentage) in (select CountryCode,max(Percentage)
                                         from CountryLanguage
                                         group by CountryCode)
group by co.Name

您可以使用窗口功能获得以下答案:

RANK 用一个 PARTITION 会成功的

With RecordedPercentages AS
(
              Select 'Aruba' Country, 'Dutch' LanguageName, Cast (5.3 as float) Percentage
    Union All Select 'Aruba', 'English', 9.5
    Union All Select 'Aruba', 'Papiamento', 76.7
    Union All Select 'Aruba', 'Spanish', 7.4
    Union All Select 'Afghanistan', 'Balochi', 0.9
    Union All Select 'Afghanistan', 'Dari', 32.1
    Union All Select 'Afghanistan', 'Pashto', 52.4
    Union All Select 'Afghanistan', 'Turkmenian', 1.9
    Union All Select 'Afghanistan', 'Uzbek', 8.8
),
MyRanking As
(
    Select 
    Country, LanguageName, Percentage, 
    Rank() Over (Partition by Country Order by Percentage Desc) Ranking
    From RecordedPercentages
)
Select *
From MyRanking
Where Ranking = 1

只需按两列添加组

SELECT co.Name, cl.language, max(cl.Percentage)
from Country co, CountryLanguage cl
where co.Code = cl.CountryCode
group by co.Name,cl.language