问题:
How to write a MYSQL function to return records based on consecutive value of a column(int or date or timestamp).
For example :
// id consecutive 4 times record ,id :...
可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:
问题:
How to write a MYSQL function to return records based on consecutive value of a column(int or date or timestamp).
For example :
// id consecutive 4 times record ,id : 5,6,7,8
select * from employee where consecutive(id,4);
// date consecutive 4 dates record
select * from employee where consecutive(date,4);
//as long as id is consecutive, return all.
select * from employee where consecutive (date,null);
回答1:
In ISO-standard SQL 2003 or later I'd use ROW_NUMBER() OVER ( ORDER BY id )
combined with OFFSET
. This query works in SQL Server 2012 or later, Oracle 10g or later, PostgreSQL, or MySQL 8 or later - but hardly anyone runs MySQL 8...
For your first example:
SELECT
employee.*
FROM
(
SELECT
id,
ROW_NUMBER() OVER ( ORDER BY id ) AS rn
FROM
employee
) AS sq
INNER JOIN employee ON employee.id = sq.id
ORDER BY
id
OFFSET
0 ROWS FETCH 4
Fortunately MySQL lets you increment variables and evaluate other impure expressions in a SELECT
as though they're evaluated iteratively (this is an incorrect assumption to make in the "SQL way of thinking").
So here's a trick to get a row-rank in MySQL, using an inline initialization:
SELECT
*,
@rank := @rank + 1 AS rn
FROM
employee,
( SELECT @rank := 0 )
ORDER BY
employee.id
So we can use this as our subquery:
SELECT
employee.*
FROM
(
SELECT
employee.id,
@rank := @rank + 1 AS rn
FROM
employee,
( SELECT @rank := 0 )
ORDER BY
employee.id
) AS sq
INNER JOIN employee ON employee.id = sq.id
ORDER BY
id
LIMIT
4 OFFSET 0