Oracle分析函数
Oracle分析函数
提问于 2015-06-30 19:39:44
我也有类似的问题。你们谁能给我一个解决方案。下面是表值
10
20
30
40我想要这样的输出
10 10-2 8
20 20-8 12
30 30-12 18
40 40-18 22 按升序和始终减法2对第一个recrod排序，并将结果用于后续记录。
回答 1
Stack Overflow用户
发布于 2015-06-30 20:09:15
SQL Fiddle
Oracle 11g R2架构设置
CREATE TABLE TEST ( VAL ) AS
SELECT 10 FROM DUAL
UNION ALL SELECT 20 FROM DUAL
UNION ALL SELECT 30 FROM DUAL
UNION ALL SELECT 40 FROM DUAL;查询1
WITH Parities AS (
SELECT VAL,
MOD( ROW_NUMBER() OVER ( ORDER BY VAL ), 2 ) AS Parity
FROM TEST
)
SELECT VAL,
ABS( SUM( CASE PARITY WHEN 1 THEN -VAL ELSE VAL END ) OVER ( ORDER BY VAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) + 2 ) AS total
FROM Parities| VAL | TOTAL |
|-----|-------|
| 10 | 8 |
| 20 | 12 |
| 30 | 18 |
| 40 | 22 |页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/31137249
复制相关文章
相似问题
腾讯云开发者
