mysql - SQL 中字段对的首次出现

Question

我的表格是这样的:

+------------+---------------+--------+
|    City    | Category Name | Orders |
+------------+---------------+--------+
| London     | Components    |     29 |
| Woolston   | Bikes         |     16 |
| Union City | Clothing      |     13 |
| London     | Bikes         |     13 |
| Union City | Bikes         |     11 |
| Union City | Components    |     11 |
| Woolston   | Clothing      |     11 |
| Woolston   | Components    |      8 |
| Woolston   | Accessories   |      8 |
| Union City | Accessories   |      8 |
| London     | Clothing      |      4 |
| London     | Accessories   |      1 |
+------------+---------------+--------+

目标是获取每个城市及其最受欢迎的类别，因此:

+------------+---------------+
|    City    | Category Name |
+------------+---------------+
| London     | Components    |
| Woolston   | Bikes         |
| Union City | Clothing      |
+------------+---------------+

在这种情况下，我需要选择第一个表中每个城市的第一次出现及其类别。

我试过使用标准:

SELECT City, Max(Orders) 
FROM Table
GROUP BY City

然而，一旦您尝试将类别名称添加到组合中，这就会开始成为问题，而且我也不希望新表中的 Orders 字段。

有没有办法很好地做到这一点？

Answer 1

您可以使用窗口函数或相关子查询:

select t.*
from t
where t.orders = (select max(t2.orders) from t t2 where t2.city = t.city);

作为窗口函数:

select t.*
from (select t.*, row_number() over (partition by city order by orders desc) as seqnum
      from t
     ) t
where seqnum = 1;