mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
123 lines
3.4 KiB
Text
123 lines
3.4 KiB
Text
create table t1 (
|
|
pk int primary key,
|
|
a int,
|
|
b int,
|
|
c char(10),
|
|
d decimal(10, 3),
|
|
e real
|
|
);
|
|
|
|
insert into t1 values
|
|
( 1, 0, 1, 'one', 0.1, 0.001),
|
|
( 2, 0, 2, 'two', 0.2, 0.002),
|
|
( 3, 0, 3, 'three', 0.3, 0.003),
|
|
( 4, 1, 2, 'three', 0.4, 0.004),
|
|
( 5, 1, 1, 'two', 0.5, 0.005),
|
|
( 6, 1, 1, 'one', 0.6, 0.006),
|
|
( 7, 2, NULL, 'n_one', 0.5, 0.007),
|
|
( 8, 2, 1, 'n_two', NULL, 0.008),
|
|
( 9, 2, 2, NULL, 0.7, 0.009),
|
|
(10, 2, 0, 'n_four', 0.8, 0.010),
|
|
(11, 2, 10, NULL, 0.9, NULL);
|
|
|
|
select pk,
|
|
lead(pk) over (order by pk),
|
|
lead(pk, 1) over (order by pk),
|
|
lead(pk, 2) over (order by pk),
|
|
lead(pk, 0) over (order by pk),
|
|
lead(pk, -1) over (order by pk),
|
|
lead(pk, -2) over (order by pk)
|
|
from t1
|
|
order by pk asc;
|
|
|
|
select pk,
|
|
lag(pk) over (order by pk),
|
|
lag(pk, 1) over (order by pk),
|
|
lag(pk, 2) over (order by pk),
|
|
lag(pk, 0) over (order by pk),
|
|
lag(pk, -1) over (order by pk),
|
|
lag(pk, -2) over (order by pk)
|
|
from t1
|
|
order by pk asc;
|
|
|
|
select pk, pk - 2,
|
|
lag(pk, pk - 2) over (order by pk),
|
|
lead(pk, pk - 2) over (order by pk)
|
|
from t1
|
|
order by pk asc;
|
|
|
|
select pk, pk - 2,
|
|
lag(pk, pk + 2) over (order by pk),
|
|
lead(pk, pk + 2) over (order by pk)
|
|
from t1
|
|
order by pk asc;
|
|
|
|
select pk, a,
|
|
lead(pk) over (partition by a order by pk),
|
|
lead(pk, 1) over (partition by a order by pk),
|
|
lead(pk, 2) over (partition by a order by pk),
|
|
lead(pk, 0) over (partition by a order by pk),
|
|
lead(pk, -1) over (partition by a order by pk),
|
|
lead(pk, -2) over (partition by a order by pk)
|
|
from t1
|
|
order by pk asc;
|
|
|
|
select pk, a,
|
|
lag(pk) over (partition by a order by pk),
|
|
lag(pk, 1) over (partition by a order by pk),
|
|
lag(pk, 2) over (partition by a order by pk),
|
|
lag(pk, 0) over (partition by a order by pk),
|
|
lag(pk, -1) over (partition by a order by pk),
|
|
lag(pk, -2) over (partition by a order by pk)
|
|
from t1
|
|
order by pk asc;
|
|
|
|
select pk, a, pk - 2,
|
|
lag(pk, pk - 2) over (partition by a order by pk),
|
|
lead(pk, pk - 2) over (partition by a order by pk),
|
|
lag(pk, a - 2) over (partition by a order by pk),
|
|
lead(pk, a - 2) over (partition by a order by pk)
|
|
from t1
|
|
order by pk asc;
|
|
|
|
select pk, a, pk - 2,
|
|
lag(pk, pk + 2) over (partition by a order by pk),
|
|
lead(pk, pk + 2) over (partition by a order by pk),
|
|
lag(pk, a + 2) over (partition by a order by pk),
|
|
lead(pk, a + 2) over (partition by a order by pk)
|
|
from t1
|
|
order by pk asc;
|
|
|
|
select pk, a, b, c, d, e,
|
|
lag(a) over (partition by a order by pk),
|
|
lag(b) over (partition by a order by pk),
|
|
lag(c) over (partition by a order by pk),
|
|
lag(d) over (partition by a order by pk),
|
|
lag(e) over (partition by a order by pk)
|
|
from t1
|
|
order by pk asc;
|
|
|
|
select pk, a, b, a+b,
|
|
lag(a + b) over (partition by a order by pk)
|
|
from t1
|
|
order by pk asc;
|
|
|
|
select pk, a, b, a+b,
|
|
lag(a + b) over (partition by a order by pk) + pk
|
|
from t1
|
|
order by pk asc;
|
|
|
|
--echo #
|
|
--echo # MDEV-15204 - LAG function doesn't require ORDER BY in OVER clause
|
|
--echo #
|
|
--error ER_NO_ORDER_LIST_IN_WINDOW_SPEC
|
|
select pk,
|
|
lag(pk, 1) over ()
|
|
from t1;
|
|
|
|
--error ER_NO_ORDER_LIST_IN_WINDOW_SPEC
|
|
select pk,
|
|
lead(pk, 1) over ()
|
|
from t1;
|
|
|
|
drop table t1;
|