MYSQL-按限制分组

MYSQL-按限制分组,第1张

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) T1WHERe rn <= 2

不幸的是,MySQL不支持该

ROW_NUMBER
语法。但是,您可以
ROW_NUMBER
使用变量进行模拟:

SELECt    rating_name, id_markets, good, neutral, badFROM (    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 T2WHERe rn <= 2ORDER 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


欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/zaji/5063380.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-11-16
下一篇 2022-11-16

发表评论

登录后才能评论

评论列表(0条)

保存