如何用MySQL查询实现基于用户标识的广告去重展示

我有一个多用户发布的广告列表，部分用户会发布多条广告（10-20条不等）。按ad_date DESC加载广告列表时，页面可能被同一用户的垃圾广告填满。

之前尝试过两种方案，但都存在问题：

• 方案一：把数据加载到PHP数组中，循环检查user_id、user_email、user_ip、user_phone，只打印每个标识对应的一条广告，跳过其余。但分页时无法确定第二页的起始位置，因为需要跟踪前一页跳过的广告数量。
• 方案二：一次性加载全部20万条广告，循环去重后分配分页内容。但PHP加载这些数据会占用约200MB内存，不可行。

我需要实现类似这样的逻辑：从ads表中查询数据，按ad_date DESC排序，且只要每个用户的一条广告（通过user_id、user_email、user_phone、user_ip任一字段判定为同一用户的，只保留最新的一条，后续广告跳过）。

示例输入 #

| ad_id | user_email        | user_phone   | ad_description | ad_date    | comment 
|-------|-------------------|--------------|----------------|------------|---------
| 1     |                   | 123-456-7891 | ad_desc1       | 2023-01-01 |
| 2     | user1@example.com | 123-456-7891 | ad_desc2       | 2023-01-02 | skip because PHONE listed before
| 3     | user1@example.com | 123-456-7892 | ad_desc3       | 2023-01-03 | skip because EMAIL listed before
| 4     | user2@example.com | 123-456-7892 | ad_desc4       | 2023-02-01 | skip because PHONE listed before
| 5     | user2@example.com |              | ad_desc5       | 2023-02-02 | skip because EMAIL listed before
| 6     | user3@example.com | 123-456-7893 | ad_desc6       | 2023-03-01 |
| 7     |                   | 123-456-7892 | ad_desc7       | 2023-03-02 | skip because PHONE listed before
| 8     | user3@example.com | 123-456-7895 | ad_desc8       | 2023-03-03 | skip because EMAIL listed before
| 9     | user3@example.com |              | ad_desc9       | 2023-03-02 | skip because EMAIL listed before
| 10    |                   | 123-456-7899 | ad_desc10      | 2023-03-02 |

预期输出 #

| ad_id | user_email        | user_phone   | ad_description | ad_date    |
|-------|-------------------|--------------|----------------|------------|
| 1     |                   | 123-456-7891 | ad_desc1       | 2023-01-01 |
| 6     | user3@example.com | 123-456-7893 | ad_desc6       | 2023-03-01 |
| 10    |                   | 123-456-7899 | ad_desc10      | 2023-03-02 |

这个需求的核心是按时间倒序遍历广告，一旦某个标识（邮箱、电话、IP、用户ID）出现过，就跳过后续包含该标识的广告。MySQL 8.0及以上版本支持递归CTE（公共表表达式），可以用它实现这个逻辑：

基础查询语句 #

WITH RECURSIVE ranked_ads AS (
    -- 按发布时间倒序给广告排序，生成序号
    SELECT 
        ad_id, user_email, user_phone, user_ip, ad_description, ad_date,
        ROW_NUMBER() OVER (ORDER BY ad_date DESC, ad_id DESC) AS rn
    FROM ads
),
unique_ads AS (
    -- 递归起始：取第一条广告，记录已出现的标识
    SELECT 
        rn, ad_id, user_email, user_phone, user_ip, ad_description, ad_date,
        JSON_ARRAY(
            IF(user_email IS NOT NULL, CONCAT('email:', user_email), NULL),
            IF(user_phone IS NOT NULL, CONCAT('phone:', user_phone), NULL),
            IF(user_ip IS NOT NULL, CONCAT('ip:', user_ip), NULL),
            IF(user_id IS NOT NULL, CONCAT('user:', user_id), NULL)
        ) AS seen_identifiers
    FROM ranked_ads
    WHERE rn = 1

    UNION ALL

    -- 递归步骤：依次检查后续广告，若所有标识都未出现过则保留
    SELECT 
        r.rn, r.ad_id, r.user_email, r.user_phone, r.user_ip, r.ad_description, r.ad_date,
        JSON_MERGE_PRESERVE(
            u.seen_identifiers,
            JSON_ARRAY(
                IF(r.user_email IS NOT NULL, CONCAT('email:', r.user_email), NULL),
                IF(r.user_phone IS NOT NULL, CONCAT('phone:', r.user_phone), NULL),
                IF(r.user_ip IS NOT NULL, CONCAT('ip:', r.user_ip), NULL),
                IF(r.user_id IS NOT NULL, CONCAT('user:', r.user_id), NULL)
            )
        ) AS seen_identifiers
    FROM ranked_ads r
    JOIN unique_ads u ON r.rn = u.rn + 1
    WHERE 
        NOT JSON_CONTAINS(u.seen_identifiers, JSON_QUOTE(CONCAT('email:', r.user_email)))
        AND NOT JSON_CONTAINS(u.seen_identifiers, JSON_QUOTE(CONCAT('phone:', r.user_phone)))
        AND NOT JSON_CONTAINS(u.seen_identifiers, JSON_QUOTE(CONCAT('ip:', r.user_ip)))
        AND NOT JSON_CONTAINS(u.seen_identifiers, JSON_QUOTE(CONCAT('user:', r.user_id)))
)
-- 输出最终去重后的广告
SELECT ad_id, user_email, user_phone, ad_description, ad_date
FROM unique_ads
ORDER BY rn;

逻辑说明 #

1. ranked_ads CTE：先将所有广告按ad_date DESC（最新在前）排序，给每条广告分配唯一序号rn，方便递归遍历。
2. unique_ads CTE：
   • 起始部分：取排序后的第一条广告，将其非空标识（邮箱、电话等）存入JSON数组seen_identifiers，记录已出现的用户标识。
   • 递归部分：依次取下一条广告，检查它的所有标识是否都不在seen_identifiers中。如果都未出现，就保留这条广告，并将其标识加入seen_identifiers；否则跳过。
3. 最终从unique_ads中提取结果，就是符合要求的去重广告列表。

分页适配 #

如果需要分页，可以给去重后的结果重新分配序号，再用LIMIT和OFFSET实现：

WITH RECURSIVE ranked_ads AS (
    SELECT 
        ad_id, user_email, user_phone, user_ip, ad_description, ad_date,
        ROW_NUMBER() OVER (ORDER BY ad_date DESC, ad_id DESC) AS rn
    FROM ads
),
unique_ads AS (
    SELECT 
        rn, ad_id, user_email, user_phone, user_ip, ad_description, ad_date,
        JSON_ARRAY(
            IF(user_email IS NOT NULL, CONCAT('email:', user_email), NULL),
            IF(user_phone IS NOT NULL, CONCAT('phone:', user_phone), NULL),
            IF(user_ip IS NOT NULL, CONCAT('ip:', user_ip), NULL),
            IF(user_id IS NOT NULL, CONCAT('user:', user_id), NULL)
        ) AS seen_identifiers
    FROM ranked_ads
    WHERE rn = 1

    UNION ALL

    SELECT 
        r.rn, r.ad_id, r.user_email, r.user_phone, r.user_ip, r.ad_description, r.ad_date,
        JSON_MERGE_PRESERVE(
            u.seen_identifiers,
            JSON_ARRAY(
                IF(r.user_email IS NOT NULL, CONCAT('email:', r.user_email), NULL),
                IF(r.user_phone IS NOT NULL, CONCAT('phone:', r.user_phone), NULL),
                IF(r.user_ip IS NOT NULL, CONCAT('ip:', r.user_ip), NULL),
                IF(r.user_id IS NOT NULL, CONCAT('user:', r.user_id), NULL)
            )
        ) AS seen_identifiers
    FROM ranked_ads r
    JOIN unique_ads u ON r.rn = u.rn + 1
    WHERE 
        NOT JSON_CONTAINS(u.seen_identifiers, JSON_QUOTE(CONCAT('email:', r.user_email)))
        AND NOT JSON_CONTAINS(u.seen_identifiers, JSON_QUOTE(CONCAT('phone:', r.user_phone)))
        AND NOT JSON_CONTAINS(u.seen_identifiers, JSON_QUOTE(CONCAT('ip:', r.user_ip)))
        AND NOT JSON_CONTAINS(u.seen_identifiers, JSON_QUOTE(CONCAT('user:', r.user_id)))
),
paginated_ads AS (
    SELECT 
        ad_id, user_email, user_phone, ad_description, ad_date,
        ROW_NUMBER() OVER (ORDER BY rn) AS page_rn
    FROM unique_ads
)
-- 取第一页，每页10条
SELECT ad_id, user_email, user_phone, ad_description, ad_date
FROM paginated_ads
WHERE page_rn BETWEEN 1 AND 10;

注意事项 #

• 确保使用MySQL 8.0及以上版本，低版本不支持递归CTE。
• 给ad_date、user_email、user_phone、user_ip、user_id建立索引，可大幅提升大数据量下的查询性能。
• 如果存在大量空值的标识字段，可以在ranked_ads的WHERE条件中过滤，减少不必要的计算。

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