去重\最大\最小
drop table lll.t_user;
CREATE TABLE lll.t_user (
id VARCHAR(35) NULL,
org_id VARCHAR(35) NULL,
name VARCHAR(200) NULL
);
INSERT INTO lll.t_user (id, org_id, name) VALUES('10', 'ty', '南京');
INSERT INTO lll.t_user (id, org_id, name) VALUES('11', 'ty', '海南');
INSERT INTO lll.t_user (id, org_id, name) VALUES('12', 'ty', '深圳');
INSERT INTO lll.t_user (id, org_id, name) VALUES('13', 'ty', '汕头');
INSERT INTO lll.t_user (id, org_id, name) VALUES('123', 'ty','海南');
INSERT INTO lll.t_user (id, org_id, name) VALUES('124', 'ty','深圳');
INSERT INTO lll.t_user (id, org_id, name) VALUES('125', 'ty','汕头');
INSERT INTO lll.t_user (id, org_id, name) VALUES( '1234','sy','海司');
INSERT INTO lll.t_user (id, org_id, name) VALUES( '1235','sy','深店');
INSERT INTO lll.t_user (id, org_id, name) VALUES( '1236','sy','汕店');
commit;
-- 笛卡尔积
select a.org_id, a.id a_id, b.id b_id
from lll.t_user a
left join lll.t_user b
on a.org_id = b.org_id
order by a.org_id;
-- 取机构的最小用户id
select a.org_id, a.id
from lll.t_user a
where not exists (select 1
from lll.t_user b
where a.org_id = b.org_id
and a.id > b.id);
-- 取 除了机构的最小用户id 的其他数据
select a.org_id, a.id
from lll.t_user a
where exists (select 1
from lll.t_user b
where a.org_id = b.org_id
and a.id > b.id);
-- 取机构的最大用户id
select a.org_id, a.id
from lll.t_user a
where not exists (select 1
from lll.t_user b
where a.org_id = b.org_id
and a.id < b.id);
-- 取 除了 机构的最大用户id 的其他数据
select a.org_id, a.id
from lll.t_user a
where exists (select 1
from lll.t_user b
where a.org_id = b.org_id
and a.id < b.id);
迭代查询父节点
drop table lll.t_org;
CREATE TABLE lll.t_org (
id VARCHAR(35) NULL,
parentid VARCHAR(35) NULL,
name VARCHAR(200) NULL
);
INSERT INTO lll.t_org (id, parentid, name) VALUES('0', null, '南京');
INSERT INTO lll.t_org (id, parentid, name) VALUES('11', '0', '海南');
INSERT INTO lll.t_org (id, parentid, name) VALUES('12', '0', '深圳');
INSERT INTO lll.t_org (id, parentid, name) VALUES('13', '0', '汕头');
INSERT INTO lll.t_org (id, parentid, name) VALUES('123', '11','海南');
INSERT INTO lll.t_org (id, parentid, name) VALUES('124', '12','深圳');
INSERT INTO lll.t_org (id, parentid, name) VALUES('125', '13','汕头');
INSERT INTO lll.t_org (id, parentid, name) VALUES( '1234','123','海司');
INSERT INTO lll.t_org (id, parentid, name) VALUES( '1235','124','深店');
INSERT INTO lll.t_org (id, parentid, name) VALUES( '1236','125','汕店');
commit;
select a.*, level lev
from lll.t_org a
start with id = '1235'
connect by id = prior parentid
order by lev;