如标题所示,在MySQL中,我想将一个表连接到它自己,偏移1行,以便我可以计算DateTime字段行之间的时间。
在T-SQL中,可以使用ROW_NUMBER()和CTE。
row_number可以在mysql中使用变量(s)模拟。举个例子MariaDB [sandbox]> select * from dates limit 10;
+------+------------+----------+------------------+-------------------+--------+
| id | dte | CalMonth | CalMonthDescLong | CalMonthDescShort | calQtr |
+------+------------+----------+------------------+-------------------+--------+
| 1 | 2000-01-01 | 1 | January | Jan | 1 |
| 2 | 2000-01-02 | 1 | January | Jan | 1 |
| 3 | 2000-01-03 | 1 | January | Jan | 1 |
| 4 | 2000-01-04 | 1 | January | Jan | 1 |
| 5 | 2000-01-05 | 1 | January | Jan | 1 |
| 6 | 2000-01-06 | 1 | January | Jan | 1 |
| 7 | 2000-01-07 | 1 | January | Jan | 1 |
| 8 | 2000-01-08 | 1 | January | Jan | 1 |
| 9 | 2000-01-09 | 1 | January | Jan | 1 |
| 10 | 2000-01-10 | 1 | January | Jan | 1 |
+------+------------+----------+------------------+-------------------+--------+
10 rows in set (0.00 sec)
this query
select s.*
,t.dte,t.rownumber1
from
(
select d.*,@rn:=@rn+1 rownumber from
(select @rn:=0) rn,dates d
) s
join
(
select d.*,@rn1:=@rn1+1 rownumber1 from
(select @rn1:=0) rn,dates d
) t on t.rownumber1 = s.rownumber + 1
limit 10
返回+-----------+------------+------------+
| id | dte | CalMonth | CalMonthDescLong | CalMonthDescShort | calQtr | rownumber | dte | rownumber1 |
+------+------------+----------+------------------+-------------------+--------+-----------+------------+------------+
| 1 | 2000-01-01 | 1 | January | Jan | 1 | 1 | 2000-01-02 | 2 |
| 2 | 2000-01-02 | 1 | January | Jan | 1 | 2 | 2000-01-03 | 3 |
| 3 | 2000-01-03 | 1 | January | Jan | 1 | 3 | 2000-01-04 | 4 |
| 4 | 2000-01-04 | 1 | January | Jan | 1 | 4 | 2000-01-05 | 5 |
| 5 | 2000-01-05 | 1 | January | Jan | 1 | 5 | 2000-01-06 | 6 |
| 6 | 2000-01-06 | 1 | January | Jan | 1 | 6 | 2000-01-07 | 7 |
| 7 | 2000-01-07 | 1 | January | Jan | 1 | 7 | 2000-01-08 | 8 |
| 8 | 2000-01-08 | 1 | January | Jan | 1 | 8 | 2000-01-09 | 9 |
| 9 | 2000-01-09 | 1 | January | Jan | 1 | 9 | 2000-01-10 | 10 |
| 10 | 2000-01-10 | 1 | January | Jan | 1 | 10 | 2000-01-11 | 11 |
+------+------------+----------+------------------+-------------------+--------+-----------+------------+------------+
10 rows in set (0.09 sec)