含COUNT与ORDER BY RAND()的跨两表MySQL子查询实现需求

Got it, let's tackle this problem with a single nested MySQL query that replaces your PHP loop approach. Here's a solution that meets all your requirements:

INSERT INTO assignments (UserId, TeammateId)
SELECT UserId, TeammateId
FROM (
    SELECT 
        u.UserId,
        c.TeammateId,
        ROW_NUMBER() OVER (PARTITION BY u.UserId ORDER BY RAND()) AS rn,
        COALESCE(a.assigned_count, 0) AS assigned_count
    FROM users u
    CROSS JOIN (
        SELECT UserId AS TeammateId FROM users
    ) c
    LEFT JOIN (
        SELECT TeammateId, COUNT(*) AS assigned_count
        FROM assignments
        GROUP BY TeammateId
    ) a ON c.TeammateId = a.TeammateId
    WHERE c.TeammateId != u.UserId
    AND COALESCE(a.assigned_count, 0) < 2
) ranked
WHERE rn <= 2;

How This Works: #

• Candidate Teammates: The inner c subquery pulls all user IDs as potential teammates.
• Track Assignment Counts: The a subquery calculates how many times each teammate has already been assigned, so we can enforce the "max 2 assignments" rule.
• Cross Join for All Possible Pairs: We join every user with every possible teammate (excluding themselves via c.TeammateId != u.UserId).
• Random Ranking: The ROW_NUMBER() window function randomizes the order of candidates for each user and assigns a row number. This lets us pick the top 2 random valid teammates per user.
• Filter Valid Picks: The outer query selects only the first 2 ranked candidates per user where the teammate hasn't been assigned 2 times already.

Notes: #

• If your assignments table has a unique constraint on (UserId, TeammateId) (which it should, per your requirement), the query will automatically skip duplicate pairs if you run it multiple times (though you might want to adjust logic if you need to refresh assignments instead of adding new ones).
• For edge cases (e.g., fewer than 3 users), the query will assign as many valid teammates as possible (e.g., 1 teammate per user if there are only 2 total users).

内容的提问来源于stack exchange，提问作者techtheatre