mysql - 每个属性值的 SQL 最大值

Question

我在 SO 上看到过几篇帖子，提到在一个属性中选择由另一个属性分组的最大值，但我无法复制结果，而且我不确定我做错了什么。

我收到一个 SQL 异常，要求我检查我的 SQL 版本的手册(使用 MySQL)。

关系

• 用户(UID、UName、状态)
• 歌曲(SID、AID、SName、发布日期)
• 艺术家(AID、姓名、描述)
• 记录(SID、UID、日期)

我正在尝试选择 2018 年的用户 ID 和最常播放的歌曲(按名称)。我的尝试如下。

我的理由是获取按用户 ID (UID) 分组的关系、按歌曲 ID (SID) 分组的每首歌曲以及每首歌曲的计数，然后加入歌曲名称和 MAX 值的过滤器，但我在这里遗漏了一些主要内容，并将我的答案与其他与类似内容相关的帖子进行比较并没有多大帮助

SELECT UID, SName
FROM 
(SELECT UID, SName, COUNT(listenDate) AS listen_date_count
    FROM record
    LEFT JOIN song ON song.SID = record.SID
    WHERE YEAR(Date) = 2018
    GROUP BY UID, SName) AS records_2018
GROUP BY UID, SName
HAVING MAX(listen_date_count);

Answer 1

这是在子查询中按用户/歌曲元组进行聚合的良好开端。我只需添加一个 NOT EXISTS 条件，以确保不存在具有最高观看次数的其他元组。

以下查询应为您提供 2018 年每位用户观看次数最多的歌曲:

SELECT *
FROM (
    SELECT r.UID, r.SID, s.SName, COUNT(*) AS listen_date_count
    FROM record r
    INNER JOIN song s ON s.SID = r.SID
    WHERE YEAR(r.Date) = 2018
    GROUP BY r.UID, r.SID, s.SName
) x WHERE NOT EXISTS (
    SELECT 1
    FROM record r1
    WHERE YEAR(r1.Date) = 2018 AND r1.UID = x.UID AND r1.SID != x.SID
    GROUP BY r1.UID, r1.SID
    HAVING COUNT(*) > x.listen_date_count
)

附注:

• group by 子句中需要 SID ；按 SName 分组是不安全的，因为具有相同名称的两首不同歌曲最终会被错误地分组在一起

• 当使用联接或相关子查询时，您需要在列名前添加相关的表前缀。这可以避免冲突，并且通常会使事情更容易理解和维护。