要在MySQL中滯后一列，首先讓我們創建一個表。創建表的查詢如下-mysql>?create?table?LagDemo

->?(

->?UserId?int,

->?UserValue?int

->?);

示例

使用insert命令在表中插入一些記錄。查詢如下-mysql>?insert?into?LagDemo?values(12,158);

mysql>?insert?into?LagDemo?values(18,756);

mysql>?insert?into?LagDemo?values(15,346);

mysql>?insert?into?LagDemo?values(87,646);

mysql>?insert?into?LagDemo?values(27,334);

mysql>?insert?into?LagDemo?values(90,968);

mysql>?insert?into?LagDemo?values(84,378);

mysql>?insert?into?LagDemo?values(85,546);

使用select語句顯示表中的所有記錄。查詢如下-mysql>?select?*from?LagDemo;

輸出結果+--------+-----------+

|?UserId?|?UserValue?|

+--------+-----------+

|?????12?|???????158?|

|?????18?|???????756?|

|?????15?|???????346?|

|?????87?|???????646?|

|?????27?|???????334?|

|?????90?|???????968?|

|?????84?|???????378?|

|?????85?|???????546?|

+--------+-----------+

8?rows?in?set?(0.00?sec)

這是在MySQL中滯后一列的查詢-mysql>?SET?@f?:?=?0;

mysql>?SET?@s?:?=?2;

mysql>?SELECT?l1.UserId,?l1.UserValue?,?l1.UserValue?/?l2.UserValue?AS?'LAG'

->?FROM

->?(SELECT?if(@f,?@f:?=?@f+1,?@f:=1)?as?RowNumber,?UserId,?UserValue?FROM?LagDemo)?AS?l1

->?LEFT?JOIN

->?(SELECT?if(@s,?@s:?=?@s+1,?@s:?=?1)?as?RowNumber,?UserId,?UserValue?FROM?LagDemo)?AS?l2

->?ON?l1.RowNumber?=?l2.RowNumber;

輸出結果+--------+-----------+--------+

|?UserId?|?UserValue?|????LAG?|

+--------+-----------+--------+

|?????12?|???????158?|???NULL?|

|?????18?|???????756?|???NULL?|

|?????15?|???????346?|?2.1899?|

|?????87?|???????646?|?0.8545?|

|?????27?|???????334?|?0.9653?|

|?????90?|???????968?|?1.4985?|

|?????84?|???????378?|?1.1317?|

|?????85?|???????546?|?0.5640?|

+--------+-----------+--------+

8?rows?in?set?(0.00?sec)