oracle - sql - 在oracle中,合并三个表

我有三个表:

Sealed的对象(“ SEC_OBJECTS_FILLING” ),

Sealed 对象(“ SEC_OBJECT_SEALING” ),

off Sealed的对象(“ SEC_OBJECT_UNPLUG” )


CREATE TABLE SEC_OBJECTS_FILLING(


 Id INTEGER NOT NULL PRIMARY KEY 


 ,NAME_OBJ VARCHAR(83) NOT NULL


 ,DATA_AREA VARCHAR(12) NOT NULL


 ,SEALS VARCHAR(3) NOT NULL


);



INSERT INTO SEC_OBJECTS_FILLING(Id,NAME_OBJ,DATA_AREA,SEALS) VALUES (1,'OBJ1"','DATA_AREA1"','No');


INSERT INTO SEC_OBJECTS_FILLING(Id,NAME_OBJ,DATA_AREA,SEALS) VALUES (2,'OBJ2"','DATA_AREA2"','No');


INSERT INTO SEC_OBJECTS_FILLING(Id,NAME_OBJ,DATA_AREA,SEALS) VALUES (3,'OBJ3"','DATA_AREA1"','No');




CREATE TABLE SEC_OBJECT_SEALING(


 Id INTEGER NOT NULL PRIMARY KEY 


 ,DATA VARCHAR(19) NOT NULL


 ,DATA_AREA VARCHAR(12) NOT NULL


 ,SEALING_OBJECT VARCHAR(40) NOT NULL


 ,SEAL_NUMBER VARCHAR(9) NOT NULL


 ,NAME_UR VARCHAR(24) NOT NULL


);


INSERT INTO SEC_OBJECT_SEALING(Id,DATA,DATA_AREA,SEALING_OBJECT,SEAL_NUMBER,NAME_UR,SEALS) VALUES (1,'2019-11-13T14:33:10','DATA_AREA1"','ODJ1','A14602635','ADMIN');


INSERT INTO SEC_OBJECT_SEALING(Id,DATA,DATA_AREA,SEALING_OBJECT,SEAL_NUMBER,NAME_UR,SEALS) VALUES (2,'2019-11-13T14:34:10','DATA_AREA2"','ODJ2','A14602636','ANDRII');


INSERT INTO SEC_OBJECT_SEALING(Id,DATA,DATA_AREA,SEALING_OBJECT,SEAL_NUMBER,NAME_UR,SEALS) VALUES (3,'2019-11-13T14:35:10','DATA_AREA1"','ODJ3','A14602637','ADMIN');


INSERT INTO SEC_OBJECT_SEALING(Id,DATA,DATA_AREA,SEALING_OBJECT,SEAL_NUMBER,NAME_UR,SEALS) VALUES (4,'2019-11-13T14:36:10','DATA_AREA2"','ODJ2','A14602637','ADMIN');


INSERT INTO SEC_OBJECT_SEALING(Id,DATA,DATA_AREA,SEALING_OBJECT,SEAL_NUMBER,NAME_UR,SEALS) VALUES (5,'2019-11-13T14:37:10','DATA_AREA1"','ODJ1','A14602638','ADMIN');




CREATE TABLE SEC_OBJECT_UNPLUG(


 Id INTEGER NOT NULL PRIMARY KEY 


 ,DATA_AREA VARCHAR(12) NOT NULL


 ,OBJECT_UNPLUG VARCHAR(40) NOT NULL


 ,SEAL_NUMBER VARCHAR(9) NOT NULL


 ,NAME_USR VARCHAR(13) NOT NULL


 ,DATA_UNPLUG VARCHAR(19) NOT NULL


);


INSERT INTO SEC_OBJECT_UNPLUG(Id,DATA_AREA,OBJECT_UNPLUG,SEAL_NUMBER,NAME_USR,DATA_UNPLUG) VALUES (1,'DATA_AREA1','OBJ1','A14602635','ADMIN','14.11.2019 10:26:05');


INSERT INTO SEC_OBJECT_UNPLUG(Id,DATA_AREA,OBJECT_UNPLUG,SEAL_NUMBER,NAME_USR,DATA_UNPLUG) VALUES (2,'DATA_AREA2','OBJ2','A14602636','ADMIN','14.11.2019 10:27:05');



我编写了一个代码,该代码本来可以告诉我实际状态,有3条记录


select lo.NAME_OBJ,


 lo.DATA_AREA,


 lo.SEALS,


 ev.DATA,


 ev.NAME_UR,


 ev.SEAL_NUMBER,


 cv.NAME_USR,


 cv.DATA_UNPLUG


 from 


 SEC_OBJECTS_FILLING lo


 LEFT JOIN SEC_OBJECT_SEALING ev 


 ON lo.NAME_OBJ = ev.SEALING_OBJECT and lo.DATA_AREA = ev.DATA_AREA


 LEFT JOIN SEC_OBJECT_UNPLUG cv


 on cv.OBJECT_UNPLUG = ev.SEALING_OBJECT and ev.DATA_AREA = cv.DATA_AREA and cv.SEAL_NUMBER = ev.SEAL_NUMBER



时间:

SEC_OBJECT_SEALING中的5行符合SEC_OBJECTS_FILLING的Join条件,在查询中,两个对象(OBJ1和OBJ3 )的行重复,因此有5行,


select lo.name_obj, lo.data_area, lo.seals, ev.data, ev.name_ur,


 ev.seal_number, ev.name_usr, ev.data_unplug


 from sec_objects_filling lo


 left join (


 select s.data, s.data_area, s.sealing_object, u.


 object_unplug, s.seal_number, s.name_ur, 


 u.data_unplug, u.name_usr


 from sec_object_sealing s 


 left join sec_object_unplug u 


 on u.data_area = s.data_area and u.seal_number = s.seal_number 


 and u.object_unplug = s.sealing_object ) ev 


 on lo.name_obj = ev.sealing_object and lo.data_area = ev.data_area and rn = 1



...