数据结构
网络
RDBMS
操作系统
Java
MS Excel
iOS
HTML
CSS
Android
Python
C 语言
C++
C#
MongoDB
MySQL
Javascript
PHP
物理
化学
生物
数学
英语
经济
心理
社会
时尚
法律如何在 MySQL 中滞后列？
要滞后 MySQL 中的某一列，首先让我们创建一个表。创建表的查询如下 −
mysql> create table LagDemo -> ( -> UserId int, -> UserValue int -> ); Query OK, 0 rows affected (1.74 sec)
示例
使用 insert 命令在表中插入一些记录。查询如下 −
mysql> insert into LagDemo values(12,158); Query OK, 1 row affected (0.61 sec) mysql> insert into LagDemo values(18,756); Query OK, 1 row affected (0.21 sec) mysql> insert into LagDemo values(15,346); Query OK, 1 row affected (0.25 sec) mysql> insert into LagDemo values(87,646); Query OK, 1 row affected (0.14 sec) mysql> insert into LagDemo values(27,334); Query OK, 1 row affected (0.11 sec) mysql> insert into LagDemo values(90,968); Query OK, 1 row affected (0.08 sec) mysql> insert into LagDemo values(84,378); Query OK, 1 row affected (0.10 sec) mysql> insert into LagDemo values(85,546); Query OK, 1 row affected (0.56 sec)
使用 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; Query OK, 0 rows affected (0.00 sec) mysql> SET @s : = 2; Query OK, 0 rows affected (0.00 sec) 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;
Learn MySQL in-depth with real-world projects through our MySQL certification course. Enroll and become a certified expert to boost your career.
输出
+--------+-----------+--------+ | 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)
更新于: 2020 年 6 月 26 日
142 次浏览
广告