如何在MySQL中无需子查询实现两次GROUP BY的等价逻辑？

Since you can’t use subqueries, a self-join approach is the perfect workaround here. The core idea is to identify each user’s minimum role by checking that no smaller role exists for the same user. Here’s the query:

SELECT ur.roleID, COUNT(DISTINCT ur.userName) AS user_count
FROM user_roles ur
LEFT JOIN user_roles ur2 
  ON ur.userName = ur2.userName 
  AND ur2.roleID < ur.roleID
WHERE ur2.userName IS NULL
GROUP BY ur.roleID;

How this works: #

• We join the user_roles table to itself (aliased as ur and ur2) on matching usernames, but only where ur2 has a smaller roleID than ur.
• The LEFT JOIN ensures we keep all rows from ur, even if there’s no matching smaller role in ur2.
• When ur2.userName IS NULL, that means there are no smaller roles for that user—so this row represents the user’s minimum role.
• Finally, we group by roleID and count distinct usernames to get how many users have that role as their lowest permission level.

If your MySQL version supports window functions (8.0+), another clean approach (using a derived table, which is often performant even if some consider it a light subquery) is:

SELECT roleID, COUNT(DISTINCT userName) AS user_count
FROM (
    SELECT userName, roleID,
           RANK() OVER (PARTITION BY userName ORDER BY roleID ASC) AS role_rank
    FROM user_roles
) ranked_roles
WHERE role_rank = 1
GROUP BY roleID;

This uses RANK() to assign a rank to each role per user (starting at 1 for the smallest roleID), then filters to only keep the top-ranked (minimum) role for each user before grouping and counting.

But the self-join method strictly avoids any subqueries, which aligns perfectly with your performance constraints.

内容的提问来源于stack exchange，提问作者Just Another Justin