mysql对多对多关系条件进行排序

Question

我有两张 table

groups:
    id          : integer
    group_title : string

group_sessions:
    id          : integer
    group_id    : integer
    start_time  : timestamp

我需要根据最近即将举行的 session start_time对组进行排序。例如，A 组明天有下一场比赛，B 组今天有下一场比赛。那么B组应该是第一。这是我写的，但显然它不起作用

select g.id, g.group_title
from groups g
left join (
    select group_id, MIN(start_time) as min_session_time
    from group_sessions
    where start_time > now()
    group by group_id
) s on g.id = s.group_id
order by min_session_time asc

Answer 1

首先，您需要一个 INNER JOIN，而不是 LEFT JOIN。其次，您不需要聚合，例如 MIN。

SELECT g.id, g.group_title, gs.start_time
FROM groups g
    INNER JOIN group_sessions gs ON gs.group_id = g.id
WHERE gs.start_time > NOW()
ORDER BY gs.start_time