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

piwo6bdm 于 2023-03-28 发布在 Mysql

关注(0)|答案(1)|浏览(128)

我有一个MySQL数据库，里面有一个person表，在这里我存储用户的个人详细信息。每个用户都属于一个组，所以我有另一个member_group表，在这里我存储他们的用户ID和他们的组ID，沿着他们的in_group_begin日期和（最终）他们的in_group_end日期。
当用户在一个组中时，in_group_end值为null。当他们离开该组时，它会填充离开日期。然后他们可能永远离开，或者只是移动到另一个组，需要在member_group表中添加新行。

因此，刚刚加入的TOM可能在member_group表中有一条记录，其中in_group_end值为NULL。
DICK可能已经存在了一段时间，并且在member_group表中有四条记录，除了最近的记录之外，所有记录中都填充了in_group_end的值。
HARRY已经离开了-他是member_group表中的两条记录，所有记录的值都填充在in_group_end列中。

现在我希望查询person表并返回一个用户列表，如果是当前用户，则返回当前组ID，如果是过去的用户，则返回他们所在的最后一个组ID。
我假设这可以在子查询中解决，但我不确定如何排序（例如ORDER BY (in_group_end = NULL) DESC, in_group_end DESC），然后还有GROUP BY，无论如何都会提前执行。member_group表有一个自动递增的tbl_id字段，所以可能是MAX(tbl_id)上的某种进一步连接或其他东西？
这个查询是什么样子的？任何帮助都很感激。

mysql

来源：https://stackoverflow.com/questions/75826518/order-subquery-results-and-return-first-row-per-id

1条答案

按热度按时间

cwtwac6a1#

你可以使用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;

赞(0）回复(0）举报 2023-03-28

我来回答

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

1条答案

相关问题

热门标签

最新问答