CREATE TABLE "AAA1"
(
"OID" VARCHAR2(50) NOT NULL,
"NAME" VARCHAR2(50),
"PID" VARCHAR2(50),
"SORT" INT,
"VERSION" CHAR(10),
PRIMARY KEY("OID", "VERSION")
);
COMMIT;
DELETE FROM AAA1;
insert into "AAA1" ("OID","NAME","PID","SORT","VERSION") values ('0', '事项', '-1', 1, '2018-01-01');
insert into "AAA1" ("OID","NAME","PID","SORT","VERSION") values ('1', '事项1', '0', 1, '2018-01-01');
insert into "AAA1" ("OID","NAME","PID","SORT","VERSION") values ('2', '事项2', '0', 2, '2018-01-01');
insert into "AAA1" ("OID","NAME","PID","SORT","VERSION") values ('11', '事项11', '1', 1, '2018-01-01');
insert into "AAA1" ("OID","NAME","PID","SORT","VERSION") values ('12', '事项12', '1', 2, '2018-01-01');
insert into "AAA1" ("OID","NAME","PID","SORT","VERSION") values ('21', '事项21', '2', 1, '2018-01-01');
insert into "AAA1" ("OID","NAME","PID","SORT","VERSION") values ('211', '事项211', '21', 1, '2018-01-01');
insert into "AAA1" ("OID","NAME","PID","SORT","VERSION") values ('212', '事项212', '21', 2, '2018-01-01');
insert into "AAA1" ("OID","NAME","PID","SORT","VERSION") values ('0', '事项', '-1', 1, '2018-03-01');
insert into "AAA1" ("OID","NAME","PID","SORT","VERSION") values ('1', '事项1', '0', 1, '2018-03-01');
insert into "AAA1" ("OID","NAME","PID","SORT","VERSION") values ('2', '事项2', '0', 2, '2018-03-01');
insert into "AAA1" ("OID","NAME","PID","SORT","VERSION") values ('11', '事项11', '1', 1, '2018-03-01');
insert into "AAA1" ("OID","NAME","PID","SORT","VERSION") values ('12', '事项12', '1', 2, '2018-03-01');
insert into "AAA1" ("OID","NAME","PID","SORT","VERSION") values ('21', '事项21', '2', 1, '2018-03-01');
insert into "AAA1" ("OID","NAME","PID","SORT","VERSION") values ('211', '事项211', '21', 1, '2018-03-01');
insert into "AAA1" ("OID","NAME","PID","SORT","VERSION") values ('212', '事项212', '21', 2, '2018-03-01');
COMMIT;
--sql1
select "OID","NAME","PID","SORT","VERSION"
from "AAA1"
where version = '2018-03-01'
connect by prior OID = PID AND VERSION = '2018-03-01'
START WITH OID = '0' AND VERSION = '2018-03-01'
order siblings by sort;
--sql2
select "OID","NAME","PID","SORT","VERSION"
from "AAA1"
--where version = '2018-03-01'
connect by prior OID = PID AND VERSION = '2018-03-01'
START WITH OID = '0' AND VERSION = '2018-03-01'
order siblings by sort;
--sql3
select "OID","NAME","PID","SORT","VERSION"
from "AAA1"
where version = '2018-03-01'
connect by prior OID = PID --AND VERSION = '2018-03-01'
START WITH OID = '0' --AND VERSION = '2018-03-01'
order siblings by sort;
--sql4
select "OID","NAME","PID","SORT","VERSION"
from "AAA1"
where version = '2018-03-01'
connect by prior OID = PID --AND VERSION = '2018-03-01'
START WITH OID = '0' AND VERSION = '2018-03-01'
order siblings by sort;
请注意,在层级表中若存在多个版本的话,只查询某个版本信息的情况时,若仅仅在where中书写where会把其他VERSION的也查询出来,connect by和start with也需要限定查询条件,只有sql1是正确的查询方式