MYSQL限制每个联接表中有1条记录

vwkv1x7d 于 2023-01-16 发布在 Mysql

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

尝试联接user_id上的两个表。users表对于每个用户具有唯一的id。user_codes表可以有多行具有相同的user_id。我只想从联接的user_codes表返回1行，其中code_count最大。

- 用户表**

| id       | email           |
| -------- | --------------- |
| 1        | user1@gmail.com |
| 2        | user2@gmail.com |
| 3        | user3@gmail.com |

- 用户代码表格**

| user_id  | invite_code | count |
| -------- | ----------- | ------|
| 1        | X49MCL1     | 40    |
| 1        | K59CLT9     | 1000  |
| 2        | X5BC924     | 15    | 
| 2        | 38DF80L     | 8     |
| 3        | 641020T     | 22    |

- 预期结果**

| id  |  email          | invite_code | count | 
| --- | --------------- | ----------- | ------|
| 1   | user1@gmail.com | K59CLT9     | 1000  |
| 2   | user2@gmail.com | X5BC924     | 15    |
| 3   | user3@gmail.com | 641020T     | 22    |

query result仅包含user_codes表中count最高的每个用户的单个示例。
下面是我能得到的最接近的查询，但它只返回第一个用户的invite_code和count。

SELECT a.id, a.email, b.invite_code, b.count
FROM users a
LEFT JOIN user_codes b
ON b.user_id = a.id 
AND b.count = (SELECT MAX(count) FROM user_codes GROUP BY b.user_id)

上面的查询返回结果：

| id  |  email          | invite_code | count  | 
| --- | --------------- | ----------- | ------ |
| 1   | user1@gmail.com | K59CLT9     | 1000   |
| 2   | user2@gmail.com | `NULL`      | `NULL` |
| 3   | user3@gmail.com | `NULL`      | `NULL` |

我似乎不明白为什么第一个记录之后的记录不包括invite_code和count。
多谢帮忙!

mysql

来源：https://stackoverflow.com/questions/75116083/mysql-limit-1-record-per-in-joined-table

1条答案

按热度按时间

4bbkushb1#

在MySQL 8+上，我建议使用RANK()窗口函数：

WITH cte AS (
    SELECT u.id, u.email, uc.invite_code, uc.count,
           RANK() OVER (PARTITION BY u.id ORDER BY uc.count DESC) rnk
    FROM users u
    INNER JOIN user_code uc
        ON uc.user_id = u.id
)

SELECT id, email, invite_code, count
FROM cte
WHERE rnk = 1;

RANK()函数还将匹配每个用户的多个记录，以获得最高计数。
通过将子查询内部的用户与外部查询相关联，您也许可以挽救当前的尝试：

SELECT a.id, a.email, b.invite_code, b.count
FROM users a
LEFT JOIN user_codes b
    ON b.user_id = a.id AND
       b.count = (SELECT MAX(uc.count) FROM user_codes uc WHERE uc.user_id = a.id);

赞(0）回复(0）举报 2023-01-16

我来回答

MYSQL限制每个联接表中有1条记录

1条答案

相关问题

热门标签

最新问答