MYSQL - Gruppieren nach Limit
Ist es eine einfache Möglichkeit, die GROUP BY-Ergebnisse nach oben zu begrenzen? 2. Die folgende Abfrage gibt alle Ergebnisse zurück. Wenn Sie 'LIMIT 2' verwenden, wird die Gesamtliste nur auf die beiden obersten Einträge reduziert.
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
Ergebnisse im Folgenden: -
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
Danke Jon
Wie gewünscht habe ich eine Kopie der Tabellenstruktur und einige Testdaten angehängt. Mein Ziel ist es, eine einzelne Ansicht zu erstellen, die aus jedem eindeutigen rating_name @ die beiden besten Ergebnisse enthäl
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);