MYSQL - 组限制 - 源码之家

问题描述：

是否有一种简单的方法来将GROUP BY结果限制在顶端2.以下查询返回所有结果。使用“限制2”将整个列表仅限于前两项。MYSQL - 组限制

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

由于乔恩

按照要求我已附加表结构和一些测试数据的副本。我的目标是创建一个具有从每一个独特的rating_name

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);

由于“GROUP BY”用于对相关数据执行集合函数，因此不太可能。 MySQL没有看到6个波兰，4个爱尔兰和6个法国小组，它看到16个不同的组，没有任何关联它们。你想达到什么目的？也许还有另一种方法来分组。 – Duncan 2010-04-15 07:27:43

你能否提供表格结构和一些测试数据？我认为有可能使用HAVING和子查询来做到这一点。 – 2010-04-15 07:39:48

答

我不认为有MySQL中的简单的方法前2结果的单一视图。要做到这一点的一种方法是，通过为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语法。当您的测试数据上运行

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

结果：但是，您可以模拟ROW_NUMBER使用变量

 
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

嗨德拉科 - 我已经更新了帖子，包括表结构和数据。我很欣赏迄今为止的所有反馈 - 谢谢。 – jono2010 2010-04-16 00:43:07

答

这仍然可以通过一个单一的查询，但它是一个有点长，而且也有一些注意事项，我会在查询后解释。尽管如此，它们在查询中并不是缺陷，就像“前两名”意义上的含糊不清一样。

这里的查询：

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_market具有相同的“好”计数相同rating_name，那么你会得到两个以上的记录。例如，如果三个爱尔兰id_markets的“好”数为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;

下午，所有非常感谢高度重视的反馈 - 非常感谢！关心乔恩 – jono2010 2010-04-19 04:34:11

答

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

表达式1可以像CONCAT（...）。参与REPLACE隐藏任何“;”。

MYSQL - 组限制

相关推荐