I'm trying to write a select statement in MySQL to get 5 rows after I get my match then sum how many times those numbers were repeated.

Example:

Raw Table

I need to find every row with the number 0, then after that select and show 5 rows after and counting the appearance of the numbers. How can I select the numbers and get the count as a result?

The result of the first select should be like this:

The result of the count for every number in the last query.

CodePudding user response：

This is a demo to get expected results:

Select numbers:

SELECT id, number
FROM (
 SELECT b.id, b.number, ROW_NUMBER() OVER(PARTITION BY a.id ORDER BY b.id ASC) r
 FROM numbers a
 JOIN numbers b ON a.id < b.id
 WHERE a.number = 0
) t WHERE t.r <= 5
ORDER BY id

Count numbers:

WITH n AS (
 SELECT id, number
 FROM (
  SELECT b.id, b.number, ROW_NUMBER() OVER(PARTITION BY a.id ORDER BY b.id ASC) r
  FROM numbers a
  JOIN numbers b ON a.id < b.id
  WHERE a.number = 0
 ) t WHERE t.r <= 5
)
SELECT number, COUNT(*) counts
FROM n
GROUP BY number

Sample data:

CREATE TABLE numbers (
id INT PRIMARY KEY auto_increment,
number INT NOT NULL
);

INSERT INTO numbers ( number ) VALUES (1),(0),(9),(14),(11),(0),(3),(4),(10),(9),(0),(5),(3),(11),(0);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1fe71080cfb27680eb2a37b721e5de2d

Update for MySQL v5.7

SELECT n.*
FROM numbers n
JOIN (
 SELECT a.id, SUBSTRING_INDEX(GROUP_CONCAT(b.id ORDER BY b.id SEPARATOR ','), ',', 5) selections
 FROM numbers a
 JOIN numbers b ON a.id < b.id
 WHERE a.number = 0
 GROUP BY a.id
) t ON FIND_IN_SET(n.id, t.selections)
ORDER BY n.id

SELECT n.number, COUNT(*) counts
FROM numbers n
JOIN (
 SELECT a.id, SUBSTRING_INDEX(GROUP_CONCAT(b.id ORDER BY b.id SEPARATOR ','), ',', 5) selections
 FROM numbers a
 JOIN numbers b ON a.id < b.id
 WHERE a.number = 0
 GROUP BY a.id
) t ON FIND_IN_SET(n.id, t.selections)
GROUP BY n.number
ORDER BY n.number

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=3be09acab5cd696ec4b01585eb5c32ed