select u.user_name,
(
select listagg(g.org_name, '-') within group(order by rownum desc )
from org g
start with g.org_id = u.org_id
connect by prior g.porg_id = g.org_id
and rownum < 4
)
from users u
where u.USER_ID = 'wj';
说明:将指定用户的信息,与该用户的机构信息遍历后的值合并的sql;
附上表,和实验结果
org 机构表
user 用户表
导出的sql如下
--------------------------------------------------------
-- 文件已创建 - 星期日-八月-23-2015
--------------------------------------------------------
--------------------------------------------------------
-- DDL for Table ORG
--------------------------------------------------------
CREATE TABLE "HR"."ORG"
( "ORG_ID" VARCHAR2(32 BYTE) DEFAULT SYS_GUID(),
"ORG_NAME" VARCHAR2(32 BYTE),
"PORG_ID" VARCHAR2(32 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Table USERS
--------------------------------------------------------
CREATE TABLE "HR"."USERS"
( "USER_ID" VARCHAR2(32 BYTE) DEFAULT sys_guid(),
"USER_NAME" VARCHAR2(32 BYTE),
"ORG_ID" VARCHAR2(32 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
COMMENT ON COLUMN "HR"."USERS"."USER_ID" IS '????';
COMMENT ON COLUMN "HR"."USERS"."USER_NAME" IS '??????';
COMMENT ON COLUMN "HR"."USERS"."ORG_ID" IS '????????id';
COMMENT ON TABLE "HR"."USERS" IS '??????';
REM INSERTING into HR.ORG
SET DEFINE OFF;
Insert into HR.ORG (ORG_ID,ORG_NAME,PORG_ID) values ('3644F8D3841047A2B9477D039876F1B9','四川总部',null);
Insert into HR.ORG (ORG_ID,ORG_NAME,PORG_ID) values ('D676FBE02AD04614AF5446908CC95264','四川分中心成都','3644F8D3841047A2B9477D039876F1B9');
Insert into HR.ORG (ORG_ID,ORG_NAME,PORG_ID) values ('CEC3BEEFB74C4510939343CE019049DA','成都营业部','D676FBE02AD04614AF5446908CC95264');
Insert into HR.ORG (ORG_ID,ORG_NAME,PORG_ID) values ('dEC3BEEFB74C4510939343CE019049DA','营业寿险部','CEC3BEEFB74C4510939343CE019049DA');
Insert into HR.ORG (ORG_ID,ORG_NAME,PORG_ID) values ('CrC3BEEFB74C4510939343CE019049DA','寿险分管经理','dEC3BEEFB74C4510939343CE019049DA');
Insert into HR.ORG (ORG_ID,ORG_NAME,PORG_ID) values ('CwC3BEEFB74C4510939343CE019049DA','寿险分管小组','dEC3BEEFB74C4510939343CE019049DA');
REM INSERTING into HR.USERS
SET DEFINE OFF;
Insert into HR.USERS (USER_ID,USER_NAME,ORG_ID) values ('zs','张珊','CwC3BEEFB74C4510939343CE019049DA');
Insert into HR.USERS (USER_ID,USER_NAME,ORG_ID) values ('yzx','杨正弦','CwC3BEEFB74C4510939343CE019049DA');
Insert into HR.USERS (USER_ID,USER_NAME,ORG_ID) values ('hsl','和侍郎','CrC3BEEFB74C4510939343CE019049DA');
Insert into HR.USERS (USER_ID,USER_NAME,ORG_ID) values ('xh','徐汇','CrC3BEEFB74C4510939343CE019049DA');
Insert into HR.USERS (USER_ID,USER_NAME,ORG_ID) values ('wj','王建','CEC3BEEFB74C4510939343CE019049DA');
Insert into HR.USERS (USER_ID,USER_NAME,ORG_ID) values ('wp','吴博','CEC3BEEFB74C4510939343CE019049DA');
--------------------------------------------------------
-- DDL for Index ORG_ID_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "HR"."ORG_ID_PK" ON "HR"."ORG" ("ORG_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Index USER_ID_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "HR"."USER_ID_PK" ON "HR"."USERS" ("USER_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
-- Constraints for Table ORG
--------------------------------------------------------
ALTER TABLE "HR"."ORG" ADD CONSTRAINT "ORG_ID_PK" PRIMARY KEY ("ORG_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE;
ALTER TABLE "HR"."ORG" MODIFY ("ORG_NAME" NOT NULL ENABLE);
ALTER TABLE "HR"."ORG" MODIFY ("ORG_ID" NOT NULL ENABLE);
--------------------------------------------------------
-- Constraints for Table USERS
--------------------------------------------------------
ALTER TABLE "HR"."USERS" ADD CONSTRAINT "USER_ID_PK" PRIMARY KEY ("USER_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE;
ALTER TABLE "HR"."USERS" MODIFY ("ORG_ID" NOT NULL ENABLE);
ALTER TABLE "HR"."USERS" MODIFY ("USER_NAME" NOT NULL ENABLE);
ALTER TABLE "HR"."USERS" MODIFY ("USER_ID" NOT NULL ENABLE);
--------------------------------------------------------
-- Ref Constraints for Table ORG
--------------------------------------------------------
ALTER TABLE "HR"."ORG" ADD CONSTRAINT "PORG_ID_PK" FOREIGN KEY ("PORG_ID")
REFERENCES "HR"."ORG" ("ORG_ID") ENABLE;
测试结果为: