sql - 选择具有最大值且两列中的值相同的行

Question

我有一个像这样的简单表格

....................................
| hotelNo | roomType | totalBooking |
....................................
| 1       | single   |    2         |
| 1       | family   |    4         |
| 2       | single   |    3         |
| 2       | family   |    2         |
| 3       | single   |    1         |
.....................................

现在我想获取每个酒店最常预订的roomType，即以下结果

......................
| hotelNo | roomType |
......................
|  1      | family   |
|  2      | single   |
|  3      | single   |
......................

P.S 我使用子查询来获取第一个表

Answer 1

如果你想要最大值，可以使用窗口函数:

select hotelNo, roomType
from (select t.*, row_number() over (partition by hotelNo order by totalBooking desc) as seqnum
      from table t
     ) t
where seqnum = 1;