代码之家 › 专栏 › 技术社区 › jono2010

mysql-按限制分组

limit group-by mysql

jono2010 · 技术社区 · 15 年前

是否有简单的方法将分组结果限制在前2位?下面的查询返回所有结果。使用“限制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

结果如下:

poland  78 48 24 12   <- keep
poland   1 15  5  0   <- keep
poland  23 12  6  3
poland   2  5  0  0
poland   3  0  5  0
poland   4  0  0  5
ireland  1  9  3  0   <- keep
ireland  2  3  0  0   <- keep
ireland  3  0  3  0
ireland  4  0  0  3
france  12 24 12  6   <- keep
france   1  3  1  0   <- keep
france 231  1  0  0
france   2  1  0  0
france   4  0  0  1
france   3  0  1  0

谢谢乔恩

根据要求,我附上了一份表结构和一些测试数据的副本。我的目标是创建一个视图,每个唯一的评级名称都有前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);

3 回复 | 直到 8 年前

Mark Byers 15 年前

我认为MySQL中没有简单的方法。实现这一点的一种方法是,为分组中分区的每一行生成一个行号,方法是对名称进行分级,然后只选择行号小于等于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 语法。但是你可以模拟 罗氏数 使用变量:

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

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

france    1  2  0  0
france    2  1  0  0
ireland   1  4  2  0
ireland  21  3  1  0
poland    1  3  1  0
poland    2  1  0  0

Marcus Adams 15 年前

这仍然可以通过一个查询来实现,但它有点长,还有一些注意事项,我将在查询后解释。不过,它们并不是查询中的缺陷,而是“前两名”的含义有些模糊。

以下是问题:

SELECT ratings.* FROM
(SELECT rating_name, 
       id_markets, 
       sum(rating_good) 'good', 
       sum(rating_neutral)'neutral', 
       sum(rating_bad) 'bad' 
 FROM zzratings 
 WHERE rating_year=year(curdate()) AND rating_week = week(curdate(),1)
 GROUP BY rating_name,id_markets) AS ratings
LEFT JOIN
(SELECT rating_name, 
       id_markets, 
       sum(rating_good) 'good', 
       sum(rating_neutral)'neutral', 
       sum(rating_bad) 'bad' 
 FROM zzratings 
 WHERE rating_year=year(curdate()) AND rating_week= week(curdate(),1)
 GROUP BY rating_name,id_markets) AS ratings2
ON ratings2.good <= ratings.good AND
  ratings2.id_markets <> ratings.id_markets AND
  ratings2.rating_name = ratings.rating_name
LEFT JOIN
(SELECT rating_name, 
       id_markets, 
       sum(rating_good) 'good', 
       sum(rating_neutral)'neutral', 
       sum(rating_bad) 'bad' 
 FROM zzratings 
 WHERE rating_year=year(curdate()) AND rating_week= week(curdate(),1)
 GROUP BY rating_name,id_markets) AS ratings3
ON ratings3.good >= ratings2.good AND
  ratings3.id_markets <> ratings.id_markets AND
  ratings3.id_markets <> ratings2.id_markets AND
  ratings3.rating_name = ratings.rating_name
WHERE (ratings2.good IS NULL OR ratings3.good IS NULL) AND
  ratings.good IS NOT NULL
ORDER BY ratings.rating_name, ratings.good DESC

需要注意的是,如果同一评级名称有多个具有相同“良好”计数的ID U市场,那么您将获得两个以上的记录。例如,如果有三个爱尔兰ID_市场的“良好”计数为3,最高,那么您如何显示前两个?不能,所以查询将显示这三个。

另外,如果有一个“3”的计数,最高的,和两个“2”的计数,你就不能显示前两个,因为你有一个并列的第二名,所以查询显示全部三个。

如果先创建一个带有聚合结果集的临时表,然后从中进行操作,则查询将更简单。

CREATE TEMPORARY TABLE temp_table
  SELECT rating_name, 
           id_markets, 
           sum(rating_good) 'good', 
           sum(rating_neutral)'neutral', 
           sum(rating_bad) 'bad' 
     FROM zzratings 
     WHERE rating_year=year(curdate()) AND rating_week= week(curdate(),1;

SELECT ratings.*
 FROM temp_table ratings
LEFT JOIN temp_table ratings2
ON ratings2.good <= ratings.good AND
  ratings2.id_markets <> ratings.id_markets AND
  ratings2.rating_name = ratings.rating_name
LEFT JOIN temp_table ratings3
ON ratings3.good >= ratings2.good AND
  ratings3.id_markets <> ratings.id_markets AND
  ratings3.id_markets <> ratings2.id_markets AND
  ratings3.rating_name = ratings.rating_name
WHERE (ratings2.good IS NULL OR ratings3.good IS NULL) AND
  ratings.good IS NOT NULL
ORDER BY ratings.rating_name, ratings.good DESC;

Ruud H.G. van Tol 8 年前

SUBSTRING_INDEX(
    GROUP_CONCAT(expr1 ORDER BY expr2 SEPARATOR ";"),
    ";",
    2  /* the GROUP_LIMIT */
)

expr1可以类似于concat(…)。包括替换以隐藏任何“;”。