lag应用实例一个

最新推荐文章于 2025-07-03 11:37:48 发布

原创最新推荐文章于 2025-07-03 11:37:48 发布 · 650 阅读

0 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

35 篇文章

订阅专栏

本文介绍如何使用SQL语句实现数据表间的连接，并通过解码函数处理特定字段值，以展示数据整合的过程与效果。具体展示了通过LEFT JOIN连接多张表，并使用DECODING函数处理数据中的缺失值，最终生成整合后的数据集。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create table a(id int,name varchar(100),qty int);
insert into a(id,name,qty) values(1,'a1',11);
insert into a(id,name,qty) values(2,'a2',22);

create table b(id int,name varchar(100),qty int,a_id int);
insert into b(id,name,qty,a_id) values(1,'b1',5,1);
insert into b(id,name,qty,a_id) values(2,'b2',6,1);
insert into b(id,name,qty,a_id) values(3,'b3',10,2);
insert into b(id,name,qty,a_id) values(4,'b4',12,2);

create table c(id int,name varchar(100),qty int,b_id int);
insert into c(id,name,qty,b_id) values(1,'c1',5,1);
insert into c(id,name,qty,b_id) values(2,'c2',2,2);
insert into c(id,name,qty,b_id) values(3,'c3',4,2);
insert into c(id,name,qty,b_id) values(4,'c4',10,3);
insert into c(id,name,qty,b_id) values(5,'c5',1,4);
insert into c(id,name,qty,b_id) values(6,'c5',2,4);
insert into c(id,name,qty,b_id) values(7,'c5',6,4);
insert into c(id,name,qty,b_id) values(8,'c5',3,4);

SQL> select * from a;

        ID NAME              QTY
---------- ---------- ----------
         1 a1                 11
         2 a2                 22

SQL> select * from b;

        ID NAME              QTY       A_ID
---------- ---------- ---------- ----------
         1 b1                  5          1
         2 b2                  6          1
         3 b3                 10          2
         4 b4                 12          2

SQL> select * from c;

        ID NAME              QTY       B_ID
---------- ---------- ---------- ----------
         1 c1                  5          1
         2 c2                  2          2
         3 c3                  4          2
         4 c4                 10          3
         5 c5                  1          4
         6 c5                  2          4
         7 c5                  6          4
         8 c5                  3          4

已选择8行。

现有如下sql:
select * from a a
left join b b on a.id =b.a_id
left join c c on c.b_id = b.id
得到的结果：

-现希望qty部分，之前出现的为空，即红色部分为null


select a.id aid,
       a.name,
       decode(a.qty,lag(a.qty) over(order by a.id,b.id,c.id),null,a.qty) aqty,
       b.id bid,
       b.name,
       decode(b.qty, lag(b.qty)over(order by a.id,b.id,c.id), null, b.qty) bqty,
       c.id cid,
       c.name,
      decode(c.qty, lag(c.qty) over(order by a.id,b.id,c.id), null, c.qty) cqty,
       c.b_id
  from a a
  left join b b
    on a.id = b.a_id
  left join c c
    on c.b_id = b.id;
	
       AID NAME       AQTY              BID NAME       BQTY              CID NAME       CQTY          B_ID
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1 a1         11                  1 b1         5                   1 c1         5                   1
         1 a1                             2 b2         6                   2 c2         2                   2
         1 a1                             2 b2                             3 c3         4                   2
         2 a2         22                  3 b3         10                  4 c4         10                  3
         2 a2                             4 b4         12                  5 c5         1                   4
         2 a2                             4 b4                             6 c5         2                   4
         2 a2                             4 b4                             7 c5         6                   4
         2 a2                             4 b4                             8 c5         3                   4

已选择8行。

SQL>