MySQL LAG/LEAD 问题 [英] MySQL LAG/LEAD issue

问题描述

我正在尝试将一些代码从我当前的主机移动到 GoDaddy，但遇到了 LEAD/LAG 问题.

I am trying to move some code from my current host to GoDaddy and am having issues with LEAD/LAG.

我的代码中有以下 SQL 语句:

I have the following SQL statement in my code:

SELECT 
  id, 
  LAG(Clients.id,1) OVER w AS 'lag', 
  LEAD(Clients.id,1) OVER w AS 'lead' 
FROM Clients 
WHERE custno IS NOT NULL 
WINDOW w AS (ORDER BY Clients.id)

在我当前的主机上，运行完美.他们正在运行 10.3.29-MariaDB.

On my current host, works perfectly. They are running 10.3.29-MariaDB.

GoDaddy 正在运行 5.6.49-cll-lve MySQL.尝试运行完全相同的查询时，我收到以下一批错误:

GoDaddy is running 5.6.49-cll-lve MySQL. I get the following batch of errors when trying to run the exact same query:

20 errors were found during analysis.

An alias was previously found. (near "w" at position 34)
Unexpected token. (near "w" at position 34)
Unrecognized keyword. (near "AS" at position 36)
Unexpected token. (near "'lag'" at position 39)
Unexpected token. (near "," at position 44)
Unexpected token. (near "LEAD" at position 46)
Unexpected token. (near "(" at position 50)
Unexpected token. (near "Clients" at position 51)
Unexpected token. (near "." at position 58)
Unexpected token. (near "id" at position 59)
Unexpected token. (near "," at position 61)
Unexpected token. (near "1" at position 62)
Unexpected token. (near ")" at position 63)
Unexpected token. (near "OVER" at position 65)
Unexpected token. (near "w" at position 70)
Unrecognized keyword. (near "AS" at position 72)
Unexpected token. (near "'lead'" at position 75)
Unrecognized keyword. (near "AS" at position 129)
Unexpected token. (near "(" at position 132)
Unexpected token. (near ")" at position 152)

有什么建议吗?

推荐答案

您在不支持窗口函数的 MySql 版本中运行此代码(您需要 MySql 8.0+).

You are running this code in a version of MySql that does not support window functions (you need MySql 8.0+).

相反，您可以使用相关子查询:

Instead you could use correlated subqueries:

SELECT 
  c.id,
  (SELECT MAX(cc.id) FROM Clients cc WHERE cc.id < c.id) AS `lag`,
  (SELECT MIN(cc.id) FROM Clients cc WHERE cc.id > c.id) AS `lead`  
FROM Clients c 
WHERE c.custno IS NOT NULL

这篇关于MySQL LAG/LEAD 问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！