1. 首页
  2. 问题
  3. MYSQL-按限制分组
一尘不染

MYSQL-按限制分组

mysql

有没有一种简单的方法可以将GROUP BY结果限制在前2位。以下查询返回所有结果。使用“ LIMIT 2”将整个列表减少到仅前2个条目。

select distinct(rating_name), 
       id_markets, 
       sum(rating_good) 'good', 
       sum(rating_neutral)'neutral', 
       sum(rating_bad) 'bad' 
 from ratings 
 where rating_year=year(curdate()) and rating_week= week(curdate(),1)
 group by rating_name,id_markets
 order by rating_name, sum(rating_good) 
 desc

结果如下：

波兰78 48 24 12 <-保持
波兰1 15 5 0 <-保持
波兰23 12 6 3
波兰2 5 0 0
波兰3 0 5 0
波兰4 0 0 5
爱尔兰1 9 3 0 <-保持
爱尔兰2 3 0 0 <-保持
爱尔兰3 0 3 0
爱尔兰4 0 0 3
法国12 24 12 6 <-保持
法国1 3 1 0 <-保持
法国231 1 0 0
法国2 1 0 0
法国4 0 0 1
法国3 0 1 0

谢谢乔恩

根据要求，我附上了表格结构和一些测试数据的副本。我的目标是创建一个视图，该视图具有每个唯一的rating_name的前2个结果

CREATE TABLE `zzratings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_markets` int(11) DEFAULT NULL,
  `id_account` int(11) DEFAULT NULL,
  `id_users` int(11) DEFAULT NULL,
  `dateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `rating_good` int(11) DEFAULT NULL,
  `rating_neutral` int(11) DEFAULT NULL,
  `rating_bad` int(11) DEFAULT NULL,
  `rating_name` varchar(32) DEFAULT NULL,
  `rating_year` smallint(4) DEFAULT NULL,
  `rating_week` tinyint(4) DEFAULT NULL,
  `cash_balance` decimal(9,6) DEFAULT NULL,
  `cash_spend` decimal(9,6) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `rating_year` (`rating_year`),
  KEY `rating_week` (`rating_week`),
  KEY `rating_name` (`rating_name`)
) ENGINE=MyISAM AUTO_INCREMENT=2166690 DEFAULT CHARSET=latin1;

INSERT INTO `zzratings` (`id`,`id_markets`,`id_account`,`id_users`,`dateTime`,`rating_good`,`rating_neutral`,`rating_bad`,`rating_name`,`rating_year`,`rating_week`,`cash_balance`,`cash_spend`)
VALUES
    (63741, 1, NULL, 100, NULL, 1, NULL, NULL, 'poland', 2010, 15, NULL, NULL),
    (63742, 1, NULL, 101, NULL, 1, NULL, NULL, 'poland', 2010, 15, NULL, NULL),
    (1, 2, NULL, 102, NULL, 1, NULL, NULL, 'poland', 2010, 15, NULL, NULL),
    (63743, 3, NULL, 103, NULL, NULL, 1, NULL, 'poland', 2010, 15, NULL, NULL),
    (63744, 4, NULL, 104, NULL, NULL, NULL, 1, 'poland', 2010, 15, NULL, NULL),
    (63745, 1, NULL, 105, NULL, 1, NULL, NULL, 'poland', 2010, 15, NULL, NULL),
    (63746, 1, NULL, 106, NULL, NULL, 1, NULL, 'poland', 2010, 15, NULL, NULL),
    (63747, 5, NULL, 100, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63748, 5, NULL, 101, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63749, 2, NULL, 102, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63750, 3, NULL, 103, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63751, 4, NULL, 104, NULL, NULL, NULL, 1, 'ireland', 2010, 15, NULL, NULL),
    (63752, 1, NULL, 105, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63753, 1, NULL, 106, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63754, 1, NULL, 100, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63755, 1, NULL, 101, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63756, 2, NULL, 102, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63757, 34, NULL, 103, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63758, 34, NULL, 104, NULL, NULL, NULL, 1, 'ireland', 2010, 15, NULL, NULL),
    (63759, 34, NULL, 105, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63760, 34, NULL, 106, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63761, 21, NULL, 100, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63762, 21, NULL, 101, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63763, 21, NULL, 102, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63764, 21, NULL, 103, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63765, 4, NULL, 104, NULL, NULL, NULL, 1, 'ireland', 2010, 15, NULL, NULL),
    (63766, 1, NULL, 105, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63767, 1, NULL, 106, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63768, 1, NULL, 100, NULL, 1, NULL, NULL, 'france', 2010, 15, NULL, NULL),
    (63769, 1, NULL, 101, NULL, 1, NULL, NULL, 'france', 2010, 15, NULL, NULL),
    (63770, 2, NULL, 102, NULL, 1, NULL, NULL, 'france', 2010, 15, NULL, NULL),
    (63771, 3, NULL, 103, NULL, NULL, 1, NULL, 'france', 2010, 15, NULL, NULL),
    (63772, 4, NULL, 104, NULL, NULL, NULL, 1, 'france', 2010, 15, NULL, NULL);

阅读 332

收藏
2020-05-17

共1个答案

一尘不染

我认为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
2020-05-17