碰到一个操蛋的分析函数问题，lead/lag() over ()

A表：

select t.*, t.rowid from a t order by to_number(a1);

	A1	A2	A3	A4	A5	A6
1	1	1	A1	A	11
2	2	2	A2	A	11
3	3	3	A3	A	11
4	4	1	B1	B	11
5	5	2	B2	B	11
6	6	1	C4	C	11
7	7	2	C3	C	11
8	8	3	C2	C	11
9	9	4	C1	C	11
10	10	1	A3	A-	11
11	11	2	A2	A-	11
12	12	3	A1	A-	11

B表：
select t.*, t.rowid from b t order by to_number(b1);

	B1	B2	B3
1	1	A1	A2
2	2	A2	A3
3	3	B1	B2
4	4	C4	C3
5	5	C3	C2
6	6	C2	C1
7	7	A3	A2
8	8	A2	A1
9	9	B2	B1
10	10	C1	C2
11	11	C2	C3
12	12	C3	C4
13	13	C4	C2

前天面试，面试官提出来了个这样操蛋的问题，如下描述：

A表中的A6字段要更新值，更新的值是从哪个来的？

A6要更新的值是由本条记录与下一级记录，是否在B表中B2,B3有对应关系确定，如果有对应关系，那么A6=B1，而且是以A4来分组的****而且是一条SQL来搞定，我操！，当时没做出来，回头上网上才找到的答案 。

例：我要更新A1='1' 的第一个记录里面的A6，那个，首先会对比A1='1'的A3与下一条 A1='2'这条记录的A3，那个，在B表中，我们可以看到第一行1 ,A1,A2 有这样的记录，那么A表中的A6=1 ，如果没有对应关系，肯定不更新了。

SQL：

merge into a
using (select b.b1, c.a1, c.a3, c.a7
         from (select a.a1,
                      a.a3,
                      lead(a3, 1) over(partition by a4 order by to_number(a1)) as a7
                 from a
                order by to_number(a1)) c,
              b
        where c.a7 = b.b3
          and c.a3 = b.b2) k
on (a.a1 = k.a1)
when matched then
  update set a.a6 = k.b1;

显示结果：

select t.*, t.rowid from a t order by to_number(a1);

	A1	A2	A3	A4	A5	A6
1	1	1	A1	A	11	1
2	2	2	A2	A	11	2
3	3	3	A3	A	11
4	4	1	B1	B	11	3
5	5	2	B2	B	11
6	6	1	C4	C	11	4
7	7	2	C3	C	11	5
8	8	3	C2	C	11	6
9	9	4	C1	C	11
10	10	1	A3	A-	11	7
11	11	2	A2	A-	11	8
12	12	3	A1	A-	11

 

看到A6的结果了么，一条就搞定了。最关键就是红色部分啦。

lead(a3, 1) over(partition by a4 order by to_number(a1)

LAG         ： 可以访问结果集中的其它行而不用进行自连接

LEAD        ： LEAD 与 LAG 相反， LEAD 可以访问组中当前行之后的行

select a.a1,
                      a.a3,
                      lead(a3, 1) over(partition by a4 order by to_number(a1)) as a7
                 from a
                order by to_number(a1)

的执行结果：

	A1	A3	A7
1	1	A1	A2
2	2	A2	A3
3	3	A3
4	4	B1	B2
5	5	B2
6	6	C4	C3
7	7	C3	C2
8	8	C2	C1
9	9	C1
10	10	A3	A2
11	11	A2	A1
12	12	A1

A7 列就是使用分析函数得到的列，lead(a3,1)就是取a3列的下1个值，如果是lead(a4,3)，那就是取a4列下的第3个值。很简单了。