Mysql 排序子查询结果并返回每个 ID 的第一行

您可以使用FIRST_VALUE()窗口函数:

SELECT DISTINCT user_id,
       FIRST_VALUE(group_id) OVER (
         PARTITION BY user_id 
         ORDER BY in_group_end IS NULL DESC, in_group_end DESC
       ) AS last_group_id
FROM member_group;

如果您想要了解用户的所有详细信息:

WITH cte AS (
    SELECT DISTINCT user_id,
           FIRST_VALUE(group_id) OVER (
             PARTITION BY user_id 
             ORDER BY in_group_end IS NULL DESC, in_group_end DESC
           ) AS last_group_id
    FROM member_group
)
SELECT p.*, c.last_group_id
FROM person AS p LEFT JOIN cte AS c -- LEFT join just in case a person was never a member of any group
ON c.user_id = p.user_id;

如果您还想要最后一个in_group_end值，则使用row_number()窗口函数会更容易:

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER (
             PARTITION BY user_id 
             ORDER BY in_group_end IS NULL DESC, in_group_end DESC
           ) AS rn
    FROM member_group
)
SELECT p.*, 
       c.user_id, c.in_group_end, c.group_id AS last_group_id
FROM person AS p LEFT JOIN cte AS c -- LEFT join just in case a person was never a member of any group
ON c.user_id = p.user_id AND c.rn = 1;