有趣的SQL，有趣的函数-CSDN博客

本文链接：https://blog.csdn.net/inthirties/article/details/4501973

作者: 三十而立
时间：2009年8月31日 6:26:23
本文出自 “inthirties（三十而立）”博客，转载请务必注明作者和保留出处http://blog.csdn.net/inthirties/archive/2009/08/31/4501973.aspx

有见csdn里一个很有趣的题，

有一个表，结构如下：
编号，姓名，时间，基本工资，奖金，扣款
1 张三 8-20 1000 1000 5
2 李四 8-20 1100 900 10
3 张三 9-20 900 1000 10
4 李四 9-20 1100 900 13
5 王五 9-20 900 100 0

如果我对8-20 和 9-20 的基本工资做异动数据查询我要求得到这样的一个临时数据视图

编号，姓名，基本工资备注
1 张三 -100
2 王五 900 新增

原帖在http://topic.csdn.net/u/20090830/20/4f135d40-a52d-4346-ac15-599b2951af39.html?seed=709316630&r=59447116#r_59447116

遇到有趣的题，当然激发我的兴趣哟，一看这样需要上下记录对照的，就想到lag和lead函数了

这里也借这个题，好好的解释一下lag和lead的用法

Lag 取前面n条的记录

lag(field, n, defaultv)

Lead取后面那条的记录

lead(field, n, defaultv)

field是取值的字段， n是前后几条， defualtv是默认值，类似nvl(lag(field, n), defaultv)

lag和lead必须和开窗函数over一起来使用，

结合着这个题目，如何使用lag/lead和over来使用了，实战甚于一切

先做数据

现在习惯用with as来做数据了

with temp as

(select '张三' name, '8-20' date1, 1000 a1, 1000 a2, 5 a3 from dual

union all

select '李四' name, '8-20' date1, 1100 a1, 900 a2, 10 a3 from dual

union all

select '张三' name, '9-20' date1, 900 a1, 1000 a2, 10 a3 from dual

union all

select '李四' name, '9-20' date1, 1100 a1, 900 a2, 13 a3 from dual

union all

select '王五' name, '9-20' date1, 900 a1, 100 a2, 0 a3 from dual)

数据如下，建立临时的视图。

下面是步骤，这里需要前后记录做比对，从而算出异动值，同时有可能只有一条记录的，时间后-时间前，所以用date1排序desc,

以user为单位，所以需要按name来partition

如下

select name,a1,date1, a1-(lead(a1, 1, 0) over(partition by name order by date1 desc)) c from temp;

NAME A1 DATE C

---- ---------- ---- ---------- --

李四 1100 9-20 0

李四 1100 8-20 1100

王五 900 9-20 900

张三 900 9-20 -100

张三 1000 8-20 1000

结果如下，现在异动数据出来了，但是，多余的记录要去掉，怎么去掉多余的记录了。

其实这里两天记录我们只用第一条就可以了。row_number就可以搞定哟。

继续

select name,a1,date1, a1-(lead(a1, 1, 0) over(partition by name order by date1 desc)) c, row_number() over(partition by name order by date1 desc) rn from temp

NAME A1 DATE C RN

---- ---------- ---- ---------- ---------- --

李四 1100 9-20 0 1

李四 1100 8-20 1100 2

王五 900 9-20 900 1

张三 900 9-20 -100 1

张三 1000 8-20 1000 2

用rn和c=0过滤就可以了，变化为

select name "姓名", c "基本工资" from (

select name,a1,date1, a1-(lead(a1, 1, 0) over(partition by name order by date1 desc)) c, row_number() over(partition by name order by date1 desc) rn from temp) where rn = 1 and c <>0

姓名基本工资

---- ----------

王五 900

张三 -100

下面该处理备注了，备注的应该是只有一条记录的，用sum或者count就可以了

sum(1) over(partition by name) 这里要特别注意开窗函数的range的问题。不能用order by在over里，否则默认的开窗的range不是full range，而是current position。

sum(1) over(partition by name order by date1)

差别

NAME A1 DATE C RN CNT

---- ---------- ---- ---------- ---------- ----------

李四 1100 9-20 0 1 1

李四 1100 8-20 1100 2 2

王五 900 9-20 900 1 1

张三 900 9-20 -100 1 1

张三 1000 8-20 1000 2 2

sum(1) over(partition by name)

NAME A1 DATE C RN CNT

---- ---------- ---- ---------- ---------- ----------

李四 1100 9-20 0 1 2

李四 1100 8-20 1100 2 2

王五 900 9-20 900 1 1

张三 900 9-20 -100 1 2

张三 1000 8-20 1000 2 2

主要是cnt的差别，这是over开窗的一个特性，很多不熟悉的朋友经常这里不知何为了。

去掉order by使用full range

最后的效果如下

select name "姓名", c "基本工资", decode(cnt, 1, '新增', '') "备注" from (select name,a1,date1, a1-(lead(a1, 1, 0) over(partition by name order by date1 desc)) c, row_number() over(partition by name order by date1 desc) rn, sum(1) over(partition by name) cnt from temp) where rn = 1 and c<>0;

姓名基本工资备注

---- ---------- ----

王五 900 新增

张三 -100

这里的题很有趣，也带出了lag lead over这样功能强大的函数。

这里还合理的利用了over的range特性，啊，做这样的题，自己也受益不浅哟，快哉，快哉。