Início / dba / Perguntas / 300088
Accepted
Googlebot
Asked: 2021-09-24 16:45:46 +0800 CST

LIMITE o número de linhas em GROUP BY e GROUP_CONCAT com ORDER

  • 772

Neste sqlfiddle ,

CREATE TABLE t1
(
id int(11) unsigned NOT NULL,
val int(11) unsigned NOT NULL,
rank mediumint(7) unsigned,
INDEX(id),
INDEX(rank),
PRIMARY KEY(id,val)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

INSERT INTO t1 (id,val,rank) VALUES (1,2,2), (1,1,1), (1,9,1), (1,5,23), 
(1,3,2), (2,2,1), (2,9,8), (2,5,0);

SELECT id,GROUP_CONCAT(CONCAT(val,'|',rank) ORDER BY rank DESC SEPARATOR ',') 
FROM t1 GROUP BY id;

Como posso LIMITAR o número de linhas retornadas por GROUP_BY?

Meu problema é que ORDERdentro GROUP_BYdeve ser idêntico a ORDERdentro GROUP_CONCAT.

Por exemplo, se estiver usando LIMIT 3, a saída deve ser

id  GROUP_CONCAT(CONCAT(val,'|',rank) ORDER BY rank DESC SEPARATOR ',')
1   5|23,3|2,2|2
2   9|8,2|1,5|0
mysql query

2 respostas

  1. Best Answer

    O MariaDB 10.3 também funciona GROUP_CONCAT(... LIMIT ) ( https://mariadb.com/kb/en/group_concat/ ) OFFSETfunciona.

    O MySQL 8.0 ainda não tem LIMIT. Veja os bugs 82616 (2016), 71467, 62186, 30098, 12544 (2005) em bugs.mysql.com

    • 2 
  2. WITH cte AS ( SELECT *, DENSE_RANK() OVER (PARTITION BY id ORDER BY rank DESC) drnk
              FROM t1 )
SELECT id,
       GROUP_CONCAT(CONCAT(val,'|',rank) ORDER BY rank DESC SEPARATOR ',') 
FROM cte
WHERE drnk <= 2
GROUP BY id;

    https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=ce501d92ef88b63bca30705037d19633

    • 1

relate perguntas