一个关于oracle将多列行合并为单行的函数listagg

lkl

浏览: 147388 次

最近访客更多访客>>

loginboot

perfect_control

heisetoufa

looker_z

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

oracle数据库

oracle listagg start with connect 函数

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;

测试结果为：