mysql - 按组获取前 10 名值(value)

Question

我有下表:

ID    |State|# of Orders
-----------------------------
1001  | CA  |   39
1002  | CA  |   100
1003  | CA  |   32
1004  | CA  |   9
1005  | IL  |   60
1006  | IL  |   67
1007  | IL  |   47
1008  | IL  |   35
1009  | MA  |   67
1010  | MA  |   23
1011  | MA  |   3
1012  | MA  |   38
1013  | MO  |   9 
1014  | MO  |   45
1015  | MO  |   75
1016  | MO  |   8
1017  | AZ  |   48
1018  | AZ  |   100
1019  | AZ  |   75
1020  | AZ  |   72

如何获得返回每个州排名最高的 5 个 ID 的结果？

select ID, State, # of Orders
from table
???

谢谢!

Answer 1

如果您可以在每个州的一行上完成所有操作，您可以:

select state, substring_index(group_concat(id order by NumOrders desc), ',', 5) as top5
from table t
group by state;

假设没有重复项，您也可以每个州一行执行此操作:

select id, state, numorders
from table t
where (select count(*)
       from table t2
       where t2.state = t.state and
             (t2.numorders > numorders or
              t2.numorders = t.numorders and t2.id <= t.id
             )
      ) <= 5;