您好, 欢迎来到 !    登录 | 注册 | | 设为首页 | 收藏本站

MYSQL-按限制分组

MYSQL-按限制分组

我认为MysqL中没有简单的方法。一种方法是通过为按rating_name分组的每一行生成一个行号,然后仅选择row_number为2或更少的行。在大多数数据库中,您可以使用以下方法进行此操作:

SELECT * FROM (
    SELECT
        rating_name,
        etc...,
        ROW_NUMBER() OVER (PARTITION BY rating_name ORDER BY good) AS rn
    FROM your_table
) T1
WHERE rn <= 2

不幸的是,MysqL不支持ROW_NUMBER语法。但是,您可以ROW_NUMBER使用变量进行模拟:

SELECT
    rating_name, id_markets, good, neutral, bad
FROM (
    SELECT
        *,
        @rn := CASE WHEN @prev_rating_name = rating_name THEN @rn + 1 ELSE 1 END AS rn,
        @prev_rating_name := rating_name
    FROM (
        SELECT
            rating_name,
            id_markets,
            SUM(COALESCE(rating_good, 0)) AS good,
            SUM(COALESCE(rating_neutral, 0)) AS neutral,
            SUM(COALESCE(rating_bad, 0)) AS bad
        FROM zzratings
        WHERE rating_year = YEAR(CURDATE()) AND rating_week = WEEK(CURDATE(), 1)
        GROUP BY rating_name, id_markets
    ) AS T1, (SELECT @prev_rating_name := '', @rn := 0) AS vars
    ORDER BY rating_name, good DESC
) AS T2
WHERE rn <= 2
ORDER BY rating_name, good DESC

对测试数据运行时的结果:

法国1 2 0 0
法国2 1 0 0
爱尔兰1 4 2 0
爱尔兰21 3 1 0
波兰1 3 1 0
波兰2 1 0 0
MySQL 2022/1/1 18:14:46 有466人围观

撰写回答


你尚未登录,登录后可以

和开发者交流问题的细节

关注并接收问题和回答的更新提醒

参与内容的编辑和改进,让解决方法与时俱进

请先登录

推荐问题


联系我
置顶