Verwenden Sie LIMIT innerhalb von GROUP BY, um N Ergebnisse pro Gruppe zu erhalten?

Könnten Sie GROUP_CONCAT aggregierte Funktion, um alle Jahre in einer einzigen Spalte, gruppiert nach id und bestellt rate:

SELECT   id, GROUP_CONCAT(year ORDER BY rate DESC) grouped_year
FROM     yourtable
GROUP BY id

Ergebnis:

-----------------------------------------------------------
|  ID | GROUPED_YEAR                                      |
-----------------------------------------------------------
| p01 | 2006,2003,2008,2001,2007,2009,2002,2004,2005,2000 |
| p02 | 2001,2004,2002,2003,2000,2006,2007                |
-----------------------------------------------------------

Und dann könnten Sie FIND_IN_SET,, der gibt die position des ersten Arguments innerhalb des zweiten, zB.

SELECT FIND_IN_SET('2006', '2006,2003,2008,2001,2007,2009,2002,2004,2005,2000');
1

SELECT FIND_IN_SET('2009', '2006,2003,2008,2001,2007,2009,2002,2004,2005,2000');
6

Unter Verwendung einer Kombination von GROUP_CONCAT und FIND_IN_SET und Filterung durch die position zurückgegeben by find_in_set, dann könnten Sie diese Abfrage verwenden, gibt nur die ersten 5 Jahre für jedes id:

SELECT
  yourtable.*
FROM
  yourtable INNER JOIN (
    SELECT
      id,
      GROUP_CONCAT(year ORDER BY rate DESC) grouped_year
    FROM
      yourtable
    GROUP BY id) group_max
  ON yourtable.id = group_max.id
     AND FIND_IN_SET(year, grouped_year) BETWEEN 1 AND 5
ORDER BY
  yourtable.id, yourtable.year DESC;

Finden fiddle hier.

Bitte beachten Sie, dass, wenn mehr als eine Zeile haben die gleiche rate, sollten Sie erwägen, GROUP_CONCAT(DISTINCT rate, UM VON der Geschwindigkeit) auf die Spalte rate statt die Jahr-Spalte.

Die maximale Länge der Zeichenfolge zurückgegeben, die durch GROUP_CONCAT ist begrenzt, so funktioniert dies auch, wenn Sie müssen Sie ein paar Datensätze für jede Gruppe.

InformationsquelleAutor der Antwort fthiella

Für mich so etwas wie

SUBSTRING_INDEX(group_concat(col_name order by desired_col_order_name), ',', N) 

funktioniert perfekt. Keine komplizierten Abfrage.

Beispiel: get top 1 für jede Gruppe

SELECT 
    *
FROM
    yourtable
WHERE
    id IN (SELECT 
            SUBSTRING_INDEX(GROUP_CONCAT(id
                            ORDER BY rate DESC),
                        ',',
                        1) id
        FROM
            yourtable
        GROUP BY year)
ORDER BY rate DESC;

InformationsquelleAutor der Antwort Vishal Kumar

Versuchen Sie dies:

SELECT h.year, h.id, h.rate 
FROM (SELECT h.year, h.id, h.rate, IF(@lastid = (@lastid:=h.id), @index:=@index+1, @index:=0) indx 
      FROM (SELECT h.year, h.id, h.rate 
            FROM h
            WHERE h.year BETWEEN 2000 AND 2009 AND id IN (SELECT rid FROM table2)
            GROUP BY id, h.year
            ORDER BY id, rate DESC
            ) h, (SELECT @lastid:='', @index:=0) AS a
    ) h 
WHERE h.indx <= 5;

InformationsquelleAutor der Antwort Saharsh Shah

Bauen die virtuellen Spalten（wie RowID im Oracle）

Tabelle:

`
CREATE TABLE `stack` 
(`year` int(11) DEFAULT NULL,
`id` varchar(10) DEFAULT NULL,
`rate` float DEFAULT NULL) 
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
`

Daten:

insert into stack values(2006,'p01',8);
insert into stack values(2001,'p01',5.9);
insert into stack values(2007,'p01',5.3);
insert into stack values(2009,'p01',4.4);
insert into stack values(2001,'p02',12.5);
insert into stack values(2004,'p02',12.4);
insert into stack values(2005,'p01',2.1);
insert into stack values(2000,'p01',0.8);
insert into stack values(2002,'p02',12.2);
insert into stack values(2002,'p01',3.9);
insert into stack values(2004,'p01',3.5);
insert into stack values(2003,'p02',10.3);
insert into stack values(2000,'p02',8.7);
insert into stack values(2006,'p02',4.6);
insert into stack values(2007,'p02',3.3);
insert into stack values(2003,'p01',7.4);
insert into stack values(2008,'p01',6.8);

SQL folgendermaßen:

select t3.year,t3.id,t3.rate 
from (select t1.*, (select count(*) from stack t2 where t1.rate<=t2.rate and t1.id=t2.id) as rownum from stack t1) t3 
where rownum <=3 order by id,rate DESC;

löschen, wenn die where-Klausel in t3, es zeigt wie diese:

HOLEN Sie sich "TOP-N-Aufnahme" --> hinzufügen der "rownum <=3" in die where-Klausel(where-Klausel t3);

WÄHLEN Sie "Jahr" --> hinzufügen der "ZWISCHEN 2000 UND 2009" in der where-Klausel(where-Klausel t3);

InformationsquelleAutor der Antwort Green Blade

Den folgenden Beitrag: sql: Sie top-N-Eintrag pro Gruppe beschreibt den komplizierten Weg, dies zu erreichen, ohne Unterabfragen.

Verbessert es auf andere Lösungen, die hier angeboten werden durch:

• Tun, alles in einer einzigen Abfrage
• Seins in der Lage, richtig zu nutzen Indizes
• Vermeidung von Unterabfragen, notorisch bekannt zu produzieren schlechte Ausführungspläne in MySQL

Es ist jedoch nicht schön. Eine gute Lösung erzielbar wäre wurden Fenster-Funktionen (aka Video-Analyse-Funktionen) aktiviert in MySQL -- aber Sie sind es nicht.
Der trick verwendet, sagte post nutzt GROUP_CONCAT, das ist manchmal beschrieben als "poor man' s Window-Funktionen für MySQL".

InformationsquelleAutor der Antwort Shlomi Noach

für diejenigen wie mich, die hatte queries time-out. Ich habe die unten zu verwenden, Grenzen und alles, was von einer bestimmten Gruppe.

DELIMITER $$
CREATE PROCEDURE count_limit200()
BEGIN
    DECLARE a INT Default 0;
    DECLARE stop_loop INT Default 0;
    DECLARE domain_val VARCHAR(250);
    DECLARE domain_list CURSOR FOR SELECT DISTINCT domain FROM db.one;

    OPEN domain_list;

    SELECT COUNT(DISTINCT(domain)) INTO stop_loop 
    FROM db.one;
    -- BEGIN LOOP
    loop_thru_domains: LOOP
        FETCH domain_list INTO domain_val;
        SET a=a+1;

        INSERT INTO db.two(book,artist,title,title_count,last_updated) 
        SELECT * FROM 
        (
            SELECT book,artist,title,COUNT(ObjectKey) AS titleCount, NOW() 
            FROM db.one 
            WHERE book = domain_val
            GROUP BY artist,title
            ORDER BY book,titleCount DESC
            LIMIT 200
        ) a ON DUPLICATE KEY UPDATE title_count = titleCount, last_updated = NOW();

        IF a = stop_loop THEN
            LEAVE loop_thru_domain;
        END IF;
    END LOOP loop_thru_domain;
END $$

durchläuft es eine Liste der domains und fügt dann nur ein limit von 200 in jedem

InformationsquelleAutor der Antwort Dev-Ria

Bitte, versuchen Sie unter "gespeicherte Prozedur". Ich habe bereits überprüft. Ich bin immer richtige Ergebnis, aber ohne Verwendung groupby.

CREATE DEFINER=`ks_root`@`%` PROCEDURE `first_five_record_per_id`()
BEGIN
DECLARE query_string text;
DECLARE datasource1 varchar(24);
DECLARE done INT DEFAULT 0;
DECLARE tenants varchar(50);
DECLARE cur1 CURSOR FOR SELECT rid FROM demo1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SET @query_string='';

      OPEN cur1;
      read_loop: LOOP

      FETCH cur1 INTO tenants ;

      IF done THEN
        LEAVE read_loop;
      END IF;

      SET @datasource1 = tenants;
      SET @query_string = concat(@query_string,'(select * from demo  where `id` = ''',@datasource1,''' order by rate desc LIMIT 5) UNION ALL ');

       END LOOP; 
      close cur1;

    SET @query_string  = TRIM(TRAILING 'UNION ALL' FROM TRIM(@query_string));  
  select @query_string;
PREPARE stmt FROM @query_string;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END

InformationsquelleAutor der Antwort Himanshu Patel