SQL运用full join连接,decode函数,nvl函数进行列转行
背景说明：
full join连接,decode函数,nvl函数,结合生产背景理解更深刻：
test_A表是现金业主某时间段的收益；test_B表是非现金业主某时间段的收益；对于业主，test_A表与test_B表的业主有交集。
现要求，求某天所有业主的各自收益。
表说明：
test_A是现金业主收益表，test_B是非现金业主收益表。v_member是业主编号，v_date是某时间段，v_mount是收益。
思路整理：
①先筛选满足某天'2020-09-24 00:00:00'的数据
test_A 满足条件V_DATE=TO_DATE('2020-09-24 00:00:00','YYYY-MM-DD HH24:MI:SS')的数据，
test_B 满足条件V_DATE=TO_DATE('2020-09-24 00:00:00','YYYY-MM-DD HH24:MI:SS')的数据.
②将"两个表满足条件的记录"都展示出来
selet * from (满足条件test_A)A full join (满足条件test_B) on A.v_member=A.v_member
③对结果集进行列转行处理
在列转行时，对于空值采用decode,nvl处理。
1、准备测试环境
1、准备测试环境 create table test_A(v_member number,v_date date,v_mount number); create table test_B(v_member number,v_date date,v_mount number); insert into test_A select rownum,to_date(trunc(dbms_random.value(to_number(to_char(sysdate-5,'J')),to_number(to_char(sysdate,'J')))),'J'),floor(dbms_random.value(0,100)) from dual connect by rownum<10; insert into test_B select rownum+4,to_date(trunc(dbms_random.value(to_number(to_char(sysdate-5,'J')),to_number(to_char(sysdate,'J')))),'J'),floor(dbms_random.value(0,100)) from dual connect by rownum<10; test_A收益表，记录展示如下： select * from test_A; V_MEMBER V_DATE V_MOUNT -------- ------------------- ------- 1 2020-09-23 00:00:00 26 2 2020-09-27 00:00:00 87 3 2020-09-25 00:00:00 33 4 2020-09-24 00:00:00 20 5 2020-09-24 00:00:00 70 6 2020-09-23 00:00:00 51 7 2020-09-24 00:00:00 16 8 2020-09-26 00:00:00 65 9 2020-09-23 00:00:00 95 test_B收益表，记录展示如下： select * from test_B; V_MEMBER V_DATE V_MOUNT -------- ------------------- ------- 5 2020-09-24 00:00:00 26 6 2020-09-27 00:00:00 41 7 2020-09-23 00:00:00 35 8 2020-09-25 00:00:00 12 9 2020-09-25 00:00:00 57 10 2020-09-25 00:00:00 10 11 2020-09-24 00:00:00 47 12 2020-09-23 00:00:00 5 13 2020-09-27 00:00:00 18
2、将满足条件的记录展示出来
select A.*,B.* from ( select * from TEST_A where V_DATE=TO_DATE('2020-09-24 00:00:00','YYYY-MM-DD HH24:MI:SS') )A full JOIN ( select * from TEST_B where V_DATE=TO_DATE('2020-09-24 00:00:00','YYYY-MM-DD HH24:MI:SS') )B on A.V_MEMBER=B.V_MEMBER V_MEMBER V_DATE V_MOUNT V_MEMBER V_DATE V_MOUNT -------- ------------------- ------- -------- ------------------- ------- 4 2020-09-24 00:00:00 20 (null) (null) (null) 5 2020-09-24 00:00:00 70 5 2020-09-24 00:00:00 26 7 2020-09-24 00:00:00 16 (null) (null) (null) (null) (null) (null) 11 2020-09-24 00:00:00 47
说明：用full join将满足条件的记录都展示出来。
满足'2020-09-24 00:00:00'的test_A有业主4,5,7;满足'2020-09-24 00:00:00'的test_B有业主5,11;test_A与test_B两表同时间业主交集：5。
3、将列转成行，展示需要的记录
处理行列转行的思路：V_MEMBER,V_DATE哪个表的值不为null就取它，若同时不为null就取test_A的字段值。V_MOUNT谁的值不为null就取谁的值，若都不为null就取两表的累计值。
①采用decode函数 select decode(A.V_MEMBER,null,B.V_MEMBER,A.V_MEMBER) V_MEMBER,decode(A.V_Date,null,B.V_Date,a.V_Date) V_Date, decode(A.V_MOUNT,null,0,A.V_MOUNT)+decode(B.V_MOUNT,NULL,0,B.V_MOUNT) Amount from ( select * from TEST_A where V_DATE=TO_DATE('2020-09-24 00:00:00','YYYY-MM-DD HH24:MI:SS') )A full JOIN ( select * from TEST_B where V_DATE=TO_DATE('2020-09-24 00:00:00','YYYY-MM-DD HH24:MI:SS') )B on A.V_MEMBER=B.V_MEMBER ②采用nvl函数 select nvl(A.V_MEMBER,B.V_MEMBER) V_MEMBER,nvl(A.V_Date,B.V_Date) V_Date, nvl(A.V_MOUNT,0)+nvl(B.V_MOUNT,0) Amount from ( select * from TEST_A where V_DATE=TO_DATE('2020-09-24 00:00:00','YYYY-MM-DD HH24:MI:SS') )A full JOIN ( select * from TEST_B where V_DATE=TO_DATE('2020-09-24 00:00:00','YYYY-MM-DD HH24:MI:SS') )B on A.V_MEMBER=B.V_MEMBER
/******************查询结果展示******************/ V_MEMBER V_DATE AMOUNT -------- ------------------- ------ 4 2020-09-24 00:00:00 20 5 2020-09-24 00:00:00 96 7 2020-09-24 00:00:00 16 11 2020-09-24 00:00:00 47
注意：业主5在'2020-09-24 00:00:00'的收益是有test_A与test_B共同贡献的。
至此，所有业主在某天的各自收益已经汇总出来。
其他问题请关注目录:https://www.cnblogs.com/handhead/
