with t1 as ( --此处写你的分组统计汇总的语句 select 'user1' as username, 100 as sale from dual union all select 'user2' as username, 150 as sale from dual ), t2 as ( --根据上面的语句修改字段名 select username, sale, row_number() over(order by sale desc) as isort from t1 )select isort,username, sale, nvl(sale - (select sale from t2 b where b.isort = a.isort + 1), 0) as dif from t2 a;
--利用lead函数就可以了,考虑到可能会有的分组情况,模拟如下 ----如果你没有分组情况,直接去掉over()里面的partition by 字段即可。 ----另:由于排名最后的一个客户下面没人了因此给了他一个默认值LEAD(SALE,1,0)为0,相减之后还是客户的SALE[SYS@myorcl] SQL>WITH T1 AS( 2 SELECT DATE'2011-01-01' d_time,'user1' AS USERNAME, 100 AS SALE FROM DUAL 3 UNION ALL 4 SELECT DATE'2011-01-01' d_time,'user2' AS USERNAME, 990 AS SALE FROM DUAL 5 UNION ALL 6 SELECT DATE'2011-01-01' d_time,'user3' AS USERNAME, 11 AS SALE FROM DUAL 7 UNION ALL 8 SELECT DATE'2011-01-01' d_time,'user4' AS USERNAME, 130 AS SALE FROM DUAL 9 UNION ALL 10 SELECT DATE'2011-01-02' d_time,'user1' AS USERNAME, 120 AS SALE FROM DUAL 11 UNION ALL 12 SELECT DATE'2011-01-02' d_time,'user2' AS USERNAME, 50 AS SALE FROM DUAL 13 UNION ALL 14 SELECT DATE'2011-01-02' d_time,'user3' AS USERNAME, 650 AS SALE FROM DUAL 15 )SELECT d_time, 16 USERNAME, 17 SALE, 18 SALE-LEAD(SALE,1,0)OVER(PARTITION BY d_time ORDER BY SALE DESC) AS diff 19 FROM t1 20 ;D_TIME USERN SALE DIFF ------------------- ----- ---------- ---------- 2011-01-01 00:00:00 user2 990 860 2011-01-01 00:00:00 user4 130 30 2011-01-01 00:00:00 user1 100 89 2011-01-01 00:00:00 user3 11 11 2011-01-02 00:00:00 user3 650 530 2011-01-02 00:00:00 user1 120 70 2011-01-02 00:00:00 user2 50 50已选择7行。
with t as ( select 'a' c1,800 c2 from dual union all select 'b' c1,1800 c2 from dual union all select 'c' c1,2200 c2 from dual union all select 'd' c1,5800 c2 from dual ) select c1,c2,lead(c2,1) over(order by c2) - c2 as diff from t
CREATE TABLE T_CLIENT_VB ( v_id NUMBER(4), v_all_vb NUMBER(10) ); INSERT INTO T_CLIENT_VB VALUES(1,100); INSERT INTO T_CLIENT_VB VALUES(2,200); INSERT INTO T_CLIENT_VB VALUES(3,300); INSERT INTO T_CLIENT_VB VALUES(4,400); INSERT INTO T_CLIENT_VB VALUES(5,500); INSERT INTO T_CLIENT_VB VALUES(6,550); INSERT INTO T_CLIENT_VB VALUES(7,580); COMMIT;SELECT A.V_ID, A.V_ALL_VB - B.V_ALL_VB, A.V_ALL_VB, B.V_ALL_VB, A.RK, B.RK FROM (SELECT V_ID, V_ALL_VB, RANK() OVER(ORDER BY V_ALL_VB DESC) AS RK FROM T_CLIENT_VB ORDER BY RK DESC) A, (SELECT V_ID, V_ALL_VB, RANK() OVER(ORDER BY V_ALL_VB DESC) AS RK FROM T_CLIENT_VB ORDER BY RK DESC) B WHERE A.RK + 1 = B.RK
with t1 as
(
--此处写你的分组统计汇总的语句
select 'user1' as username, 100 as sale from dual
union all
select 'user2' as username, 150 as sale from dual
),
t2 as
(
--根据上面的语句修改字段名
select username, sale, row_number() over(order by sale desc) as isort from t1
)select isort,username, sale,
nvl(sale - (select sale from t2 b where b.isort = a.isort + 1), 0) as dif
from t2 a;
--利用lead函数就可以了,考虑到可能会有的分组情况,模拟如下
----如果你没有分组情况,直接去掉over()里面的partition by 字段即可。
----另:由于排名最后的一个客户下面没人了因此给了他一个默认值LEAD(SALE,1,0)为0,相减之后还是客户的SALE[SYS@myorcl] SQL>WITH T1 AS(
2 SELECT DATE'2011-01-01' d_time,'user1' AS USERNAME, 100 AS SALE FROM DUAL
3 UNION ALL
4 SELECT DATE'2011-01-01' d_time,'user2' AS USERNAME, 990 AS SALE FROM DUAL
5 UNION ALL
6 SELECT DATE'2011-01-01' d_time,'user3' AS USERNAME, 11 AS SALE FROM DUAL
7 UNION ALL
8 SELECT DATE'2011-01-01' d_time,'user4' AS USERNAME, 130 AS SALE FROM DUAL
9 UNION ALL
10 SELECT DATE'2011-01-02' d_time,'user1' AS USERNAME, 120 AS SALE FROM DUAL
11 UNION ALL
12 SELECT DATE'2011-01-02' d_time,'user2' AS USERNAME, 50 AS SALE FROM DUAL
13 UNION ALL
14 SELECT DATE'2011-01-02' d_time,'user3' AS USERNAME, 650 AS SALE FROM DUAL
15 )SELECT d_time,
16 USERNAME,
17 SALE,
18 SALE-LEAD(SALE,1,0)OVER(PARTITION BY d_time ORDER BY SALE DESC) AS diff
19 FROM t1
20 ;D_TIME USERN SALE DIFF
------------------- ----- ---------- ----------
2011-01-01 00:00:00 user2 990 860
2011-01-01 00:00:00 user4 130 30
2011-01-01 00:00:00 user1 100 89
2011-01-01 00:00:00 user3 11 11
2011-01-02 00:00:00 user3 650 530
2011-01-02 00:00:00 user1 120 70
2011-01-02 00:00:00 user2 50 50已选择7行。
( select 'a' c1,800 c2 from dual union all
select 'b' c1,1800 c2 from dual union all
select 'c' c1,2200 c2 from dual union all
select 'd' c1,5800 c2 from dual
)
select c1,c2,lead(c2,1) over(order by c2) - c2 as diff from t
CREATE TABLE T_CLIENT_VB
(
v_id NUMBER(4),
v_all_vb NUMBER(10)
);
INSERT INTO T_CLIENT_VB VALUES(1,100);
INSERT INTO T_CLIENT_VB VALUES(2,200);
INSERT INTO T_CLIENT_VB VALUES(3,300);
INSERT INTO T_CLIENT_VB VALUES(4,400);
INSERT INTO T_CLIENT_VB VALUES(5,500);
INSERT INTO T_CLIENT_VB VALUES(6,550);
INSERT INTO T_CLIENT_VB VALUES(7,580);
COMMIT;SELECT A.V_ID, A.V_ALL_VB - B.V_ALL_VB, A.V_ALL_VB, B.V_ALL_VB, A.RK, B.RK
FROM (SELECT V_ID, V_ALL_VB, RANK() OVER(ORDER BY V_ALL_VB DESC) AS RK
FROM T_CLIENT_VB
ORDER BY RK DESC) A,
(SELECT V_ID, V_ALL_VB, RANK() OVER(ORDER BY V_ALL_VB DESC) AS RK
FROM T_CLIENT_VB
ORDER BY RK DESC) B
WHERE A.RK + 1 = B.RK