mysql 分组取每组按条件排序后的第一条记录

查询每个项目下的按房间号排序的第一个房间

room表通过project_id字段与project表关联

MySQL8.0

# ROW_NUMBER() 函数mysql8才支持
select * from (
    select 
    ROW_NUMBER()over(partition by p.id order by r.number ASC) rowId,
    p.id,
    p.`name`,
    r.id room_id,
    r.number
    from project p
    inner join room r on r.project_id=p.id
) where rowId=1;

MySQL5.7

select * from
(select a.*, @rowId:= CASE WHEN @pid = a.project_id THEN @rowId+ 1 ELSE 1 END AS rowId,
@pid:= a.project_id as pid
from 
(select 
    p.id project_id,
    p.`name`,
    r.id room_id,
    r.number
    from project p
    inner join room r on r.project_id=p.id
    order by p.id,r.number ASC) a
)aa where rowId=1;

PS : 也可以改成前几条数据(rowId<=5,取前5条)