日期:2014-05-17 浏览次数:20746 次
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