問題描述
我正在嘗試將一些代碼從我當前的主機移動到 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 問題的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！