A MYSQL Function Return records based on consecutive value of column

问题: 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
  • 发表于 2018-07-05 11:56
  • 阅读 ( 264 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除