mysql - 根据另一列的 MAX 从一列中选择 DISTINCT

Question

我有一个查询返回每个地区用户的相对事件。我想返回该列表，但每个用户只在 1 个区域，所以我想过滤掉每个人的 MAX 应用程序。

当前查询:

SELECT 
    r.region_id,
    ha.user_id,
    count(ha.user_id) AS applications 
FROM 
    sit_applications ha
LEFT JOIN 
    listings_regions r 
        ON 
            r.listingID = ha.listingID
            AND deleted = 0
WHERE 
    ha.datetime_applied >= (NOW() - INTERVAL 1 MONTH) 
GROUP BY 
    ha.user_id, r.region_id
HAVING 
    applications > 0
ORDER BY 
    r.region_id DESC

我需要过滤这个查询，所以我只抓取每个 user_id 一次，并且它是一个地区最大的应用程序。这样我就有了每个地区所有表现最佳的列表，没有重复的用户。

Answer 1

在 MySQL 中，您可以使用三种基本方法来执行此操作:

• 使用变量
• 使用复杂的join
• 使用 substring_index() 和 group_concat() 进行破解。

复杂的 join 当你有聚合查询时真的是一团糟。破解很有趣，但也有其局限性。那么，让我们考虑变量方法:

SELECT ur.*
FROM (SELECT ur.*,
             (@rn := if(@u = user_id, @rn + 1,
                        if(@u := user_id, 1, 1)
                       )
             ) as rn
      FROM (SELECT r.region_id, ha.user_id, count(ha.user_id) AS applications 
            FROM sit_applications ha LEFT JOIN 
                 listings_regions r 
                 ON r.listingID = ha.listingID AND deleted = 0
            WHERE ha.datetime_applied >= (NOW() - INTERVAL 1 MONTH) 
            GROUP BY ha.user_id, r.region_id
            HAVING applications > 0
           ) ur CROSS JOIN
           (SELECT @u := -1, @rn := 0) params
      ORDER BY user_id, applications DESC
     ) ur
WHERE rn = 1;

注意:您的查询的某些方面并没有真正意义，即使我将它们留在其中。您正在使用 LEFT JOIN，因此 r.region_id 可能是 NULL —— 这通常是不可取的。您有一个完全没有必要的 HAVING 子句，因为 COUNT() 始终为 1——假设 ha.user_id 永远不会 空。我怀疑逻辑可以替换为 INNER JOIN、没有 HAVING 子句和 COUNT(*)。