如何编写MYSQL函数以基于列的连续值(int或date或timestamp)返回记录。
例如 :
// id连续记录4次,id:5,6,7,8
select * from employee where consecutive(id,4);
//日期连续4个日期记录
select * from employee where consecutive(date,4);
//只要id是连续的,就全部返回。
select * from employee where consecutive (date,null);
在ISO标准的SQL 2003或更高版本中,我将ROW_NUMBER() OVER ( ORDER BY id )
与结合使用OFFSET
。此查询可在SQL Server 2012或更高版本,Oracle 10g或更高版本,PostgreSQL或MySQL 8或更高版本中运行-但几乎没有人运行MySQL 8 ...
对于第一个示例:
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
幸运的是,MySQL使您可以递增变量并以迭代方式求值其他不纯表达式SELECT
(这在“ SQL思维方式”中是不正确的假设)。
SELECT
*,
@rank := @rank + 1 AS rn
FROM
employee,
( SELECT @rank := 0 )
ORDER BY
employee.id
因此,我们可以将其用作子查询:
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
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句