mysql - 选择前 5 个分组依据和排序依据

Question

我有一个这样的表:

CITY                            QNT EXP RATE
LONDON                          60  6   900
LONDON                          35  8   337
LONDON                          24  6   300
LONDON                          22  6   266
BIRMINGHAM                      22  6   266
NEWYORK                         69  19  263
LONDON                          21  6   250
ROME                            24  7   242
BIRMINGHAM                      24  7   242
BIRMINGHAM                      24  7   242
LONDON                          20  6   233
BIRMINGHAM                      23  7   228
STUTTGART                       29  9   222
LONDON                          19  6   216
STUTTGART                       25  8   212
PARIS                           31  10  210
STUTTGART                       34  11  209
STUTTGART                       34  11  209
BIRMINGHAM                      18  6   200
BIRMINGHAM                      18  6   200
NEWYORK                         18  6   200
BIRMINGHAM                      17  6   183
LONDON                          19  7   171
MUNICH                          16  6   166
PARIS                           21  8   162
STUTTGART                       39  15  160
BARCELONA                       18  7   157
LONDON                          18  7   157
ROME                            33  13  153
BARCELONA                       15  6   150
PARIS                           25  10  150
ROME                            20  8   150
PARIS                           25  10  150
ROME                            20  8   150
LONDON                          15  6   150
MUNICH                          15  6   150
BIRMINGHAM                      15  6   150
NEWYORK                         15  6   150
LONDON                          17  7   142
MUNICH                          17  7   142

这是我的 sql 命令:

select CITY, QNT, EXP, (QNT-EXP)*100/EXP as RATE
from tbl_city
order by RATE desc 

我想按城市对这些结果进行分组。但我做不到。我希望 top5 行变化最大。

结果应该是这样的:

LONDON                          60  6   900
BIRMINGHAM                      22  6   266
NEWYORK                         69  19  263
ROME                            24  7   242
STUTTGART                       29  9   222

Answer 1

这是来自MySQL 5.6。

select CITY, QNT, EXP, (QNT-EXP)*100/EXP as RATE
from tbl_city GROUP BY CITY
order by RATE desc LIMIT 5

在此链接中测试:http://www.sqlfiddle.com/#!9/3f1ea1/1

这是来自MS SQL Server 2017

select TOP 5 f.CITY, f.QNT, f.EXP, x.RATE
from (
select  CITY, MAX((QNT-EXP)*100/EXP) as RATE
from tbl_city GROUP BY CITY
) as x inner join tbl_city as f on f.CITY = x.CITY 
and ((f.QNT-f.EXP)*100/f.EXP) = x.RATE
ORDER BY RATE DESC;

在此链接中测试:http://www.sqlfiddle.com/#!18/7b8da/61