假设有100个客户，通过分组统计得到每个客户的营业额，现在想求，第一名比第二名高出多少，第二名比第三名高出多少....以此类推，最终结果显示为一个客户一行，同时显示营业额以及超出下一客户的量。
------解决方案--------------------
- SQL code
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;
------解决方案--------------------
- SQL code
--利用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 8602011-01-01 00:00:00 user4 130 302011-01-01 00:00:00 user1 100 892011-01-01 00:00:00 user3 11 112011-01-02 00:00:00 user3 650 5302011-01-02 00:00:00 user1 120 702011-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
------解决方案--------------------
- SQL code
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