如何在mysql中执行滞后操作 [英] how to do lag operation in mysql

问题描述

伙计们，我想在mysql中使用解析函数滞后.在Oracle中受支持，但在Mysql中无法做到.那么有人可以帮助我如何在Mysql中执行滞后运算吗? 例如

Guys I want to use analytical function lag in mysql. In Oracle it is supported but I can't do it in Mysql. So can anybody help me how to perform lag operation in Mysql? For example

UID                        Operation
 1                         Logged-in
 2                         View content
 3                         Review

我想使用滞后函数，以便输出如下

I want to use lag function so that my output would be as follows

UID                        Operation              Lagoperation
 1                         Logged-in                
 2                         View content           Logged-in
 3                         Review                 View content

Mysql是否支持滞后功能?

Does Mysql support lag function???

推荐答案

您可以使用用户变量进行仿真:

You can emulate it with user variables:

select uid, operation, previous_operation from (
select
y.*
, @prev AS previous_Operation
, @prev := Operation
from
your_table y
, (select @prev:=NULL) vars
order by uid
) subquery_alias

• 看到它在 sqlfiddle 实时运行中
• see it working in an sqlfiddle live

在这里初始化变量.这与在编写查询之前写SET @prev:=NULL;相同.

Here you initialize your variable(s). It's the same as writing SET @prev:=NULL; before writing your query.

, (select @prev:=NULL) vars

然后这些语句在select子句中的顺序很重要:

Then the order of these statements in the select clause is important:

, @prev AS previous_Operation
, @prev := Operation

第一个仅显示变量值，第二个将当前行的值分配给变量.

The first just displays the variables value, the second assigns the value of the current row to the variable.

具有ORDER BY子句也很重要，因为否则输出是不确定的.

It's also important to have an ORDER BY clause, as the output is otherwise not deterministic.

出于美学原因，所有这些都被放入子查询中，...以对此进行过滤

All this is put into a subquery just out of aesthetic reasons,... to filter out this

, @prev := Operation

列.

这篇关于如何在mysql中执行滞后操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！