name point ---------- steve 207 Mary 203 steve 205 steve 200 steve 139 Mary 300
我想得到条件eqn = [(第1行) – 行]的行之间的差异,其中name = steve,所以我理想地希望它是,
steve 2 //207 - 0 steve 5 //205 - 207 steve 61 //200 - 205 steve 139 //139 - 200
解决方法
编辑到期OP评论我已更改查询以完全符合您的请求.
LAG可以帮助您：
select name,coalesce(
point - lag( point,1,Null ) over (order by point),point)
as diff,point || '-' ||
LEAD( point,0 ) over (order by point) as prev_point
from t
where name = 'steve'
order by point desc
| NAME | DIFF | PREV_POINT | ----------------------------- | steve | 2 | 207-0 | | steve | 5 | 205-207 | | steve | 61 | 200-205 | | steve | 139 | 139-200 |
