mysql - 选择具有最大值的行数

Question

工作示例:http://sqlfiddle.com/#!9/80995/20

我有三个表，一个用户表、一个 user_group 表和一个链接表。

链接表包含用户添加到用户组的日期。我需要一个查询来返回当前每个组中的用户数。最近的日期决定了用户当前所在的组。

SELECT
  user_groups.name, 
  COUNT(l.name) AS ct, 
  GROUP_CONCAT(l.`name` separator  ", ") AS members 
FROM user_groups
  LEFT JOIN 
    (SELECT MAX(added), group_id, name FROM link LEFT JOIN users ON users.id = link.user_id GROUP BY user_id) l 
    ON l.group_id = user_groups.id
GROUP BY user_groups.id

我的问题是我编写的查询是否可以优化，或者写得更好。

谢谢!本

Answer 1

您实际的查询并没有给您想要的答案；至少，据我了解你的问题。 John 实际上于 2017 年 1 月 5 日加入了第 2 组，但在您的结果中却显示在第 1 组(他于 2017 年 1 月 1 日加入)。另请注意，您缺少一个组 4。

<小时/>

使用标准 SQL，我认为下一个查询就是您要查找的内容。查询中的注释应阐明每个部分正在做什么:

SELECT
    user_groups.name AS group_name, 
    COUNT(u.name) AS member_count, 
    group_concat(u.name separator ', ') AS members 
FROM 
    user_groups
    LEFT JOIN
    (
       SELECT * FROM 
       (-- For each user, find most recent date s/he got into a group
       SELECT 
           user_id AS the_user_id, MAX(added) AS last_added
       FROM 
           link 
       GROUP BY 
           the_user_id
       ) AS u_a
       -- Join back to the link table, so that the `group_id` can be retrieved
       JOIN link l2 ON l2.user_id = u_a.the_user_id AND l2.added = u_a.last_added
    ) AS most_recent_group ON most_recent_group.group_id = user_groups.id

    -- And get the users...
    LEFT JOIN users u ON u.id = most_recent_group.the_user_id
GROUP BY 
   user_groups.id, user_groups.name
ORDER BY
   user_groups.name ;

这可以在 MySQL 中以更紧凑的方式编写(滥用这样的事实，即在旧版本的 MySQL 中，它不遵循 GROUP BY 限制的 SQL 标准)。

这就是你会得到的:

group_name | member_count | members       :--------- | -----------: | :-------------Group 1    |            2 | Mikie, DominicGroup 2    |            2 | John, Paddy   Group 3    |            0 | null          Group 4    |            1 | Nellie        

dbfiddle here

---

Note that this query can be simplified if you use a database with window functions (such as MariaDB 10.2). Then, you can use:

SELECT
    user_groups.name AS group_name, 
    COUNT(u.name) AS member_count, 
    group_concat(u.name separator ', ') AS members 
FROM 
    user_groups
    LEFT JOIN
    (
        SELECT 
            user_id AS the_user_id, 
            last_value(group_id) OVER (PARTITION BY user_id ORDER BY added) AS group_id
        FROM
            link
        GROUP BY
            user_id
    ) AS most_recent_group ON most_recent_group.group_id = user_groups.id

    -- And get the users...
    LEFT JOIN users u ON u.id = most_recent_group.the_user_id
GROUP BY 
   user_groups.id, user_groups.name
ORDER BY
   user_groups.name ;

dbfiddle here