有以下数据
| deptname | phone | username | isboss |
| 部门A | 电话1 | 员工A | 0 |
| 部门A | 电话1 | 领导A | 1 |
| 部门B | 电话2 | 员工B | 0 |
| 部门B | 电话2 | 员工C | 0 |
| 部门B | 电话2 | 领导D | 1 |
需要展示效果
| deptname | phone | boss | emp |
| 部门A | 电话1 | 领导A | 员工A |
| 部门B | 电话2 | 领导D | 员工B,员工C |
因为是oracle10g的原因,所以使用了wm_concat函数,但对wm_concat结果长度有限制,超出会报ORA-22922:不存在的LOB值,这是后话,先贴代码
Select t5.deptname, Max(t5.tel), dbms_lob.substr(wmsys.wm_concat(To_Char(t5.boss))) boss, dbms_lob.substr(wmsys.wm_concat(To_Char(t5.notboss))) notboss From (Select t1.NAME deptname, t1.TEL tel, Case t2.ISBOSS When 1 Then t2.TRUE_NAME End boss, Case t2.ISBOSS When 0 Then t2.TRUE_NAME End notboss From t_duty t Left Join t_department t1 On t.DEP_ID = t1.DEP_ID Left Join t_contacts t2 On t.CONTACTS_ID = t2.CONTACTS_ID Where t.DUTY_TIME = Trunc(SysDate) ) t5 Group By t5.deptname
同样的情况的如果是oracle11gR2版本可以用,据说可以解决上面的报错问题,这个有待以后测试
但是我现在的是10g的,而且会报上面的错误,网上的一些解决方案都没解决,有人写了自定义函数,亲测完美解决
原出处已经找不到了,就不贴链接了,但还是非常感谢解决了这个问题
create or replace TYPE zh_concat_imAUTHID CURRENT_USER AS OBJECT( CURR_STR VARCHAR2(32767), STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im, P1 IN VARCHAR2) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh_concat_im, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im, SCTX2 IN zh_concat_im) RETURN NUMBER);/create or replace TYPE BODY zh_concat_imIS STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im) RETURN NUMBER IS BEGIN SCTX := zh_concat_im(NULL) ; RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im, P1 IN VARCHAR2) RETURN NUMBER IS BEGIN IF(CURR_STR IS NOT NULL) THEN CURR_STR := CURR_STR || ':' || P1; ELSE CURR_STR := P1; END IF; RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh_concat_im, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS BEGIN RETURNVALUE := CURR_STR ; RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im, SCTX2 IN zh_concat_im) RETURN NUMBER IS BEGIN IF(SCTX2.CURR_STR IS NOT NULL) THEN SELF.CURR_STR := SELF.CURR_STR || ':' || SCTX2.CURR_STR ; END IF; RETURN ODCICONST.SUCCESS; END;END;/create or replace FUNCTION zh_concat(P1 VARCHAR2)RETURN VARCHAR2 AGGREGATE USING zh_concat_im ;/