我有下表:
id username
1 Jack
2 Will
3 Grace
4 Niv
我想编写一个三列查询,根据字典顺序显示用户名,之前的用户名和之后的用户名。
含义:
before username after
Grace Jack
Grace Jack Niv
Jack Niv Will
Nive Will
我写了这个查询:
select lag(username,1) over (partition by username order by username ) as before,
username,
lead(username,1) over (partition by username order by username ) as after
from users
order by username
但它不起作用。它仅在用户名列中显示数据。我究竟做错了什么?
您应该删除PARTITION BY
:
SELECT
LAG(username, 1) OVER (ORDER BY username) AS before,
username,
LEAD(username, 1) OVER (ORDER BY username) AS after
FROM users
ORDER BY username;
如果重复,则users(username)
可以添加DISTINCT
:
SELECT ...
FROM (SELECT DISTINCT username FROM users) AS sub
ORDER BY ...;
编辑:
但它不起作用。它仅在用户名列中显示数据。我究竟做错了什么?
当您使用partition by时,您基本上将行分成了共享相同PARTITION BY
表达式值的组。因此,您的窗口仅包含一个值。这就是为什么您会得到NULL(没有上一个/下一个值)的原因。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句