ORCAL WMSYS.WM_CONCAT 函数应用实例及Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]问题出现解决

SQL> create table IDTABLE

2 (

3 id number,

4 val varchar2(20)

5 )

6 ;

Table created

SQL>

SQL> insert into IDTABLE (ID, VAL)

2 values (10, 'abc' );

1 row inserted

SQL> insert into IDTABLE (ID, VAL)

2 values (10, 'abc' );

1 row inserted

SQL> insert into IDTABLE (ID, VAL)

2 values (10, 'def' );

1 row inserted

SQL> insert into IDTABLE (ID, VAL)

2 values (10, 'def' );

1 row inserted

SQL> insert into IDTABLE (ID, VAL)

2 values (20, 'ghi' );

1 row inserted

SQL> insert into IDTABLE (ID, VAL)

2 values (20, 'jkl' );

1 row inserted

SQL> insert into IDTABLE (ID, VAL)

2 values (20, 'mno' );

1 row inserted

SQL> insert into IDTABLE (ID, VAL)

2 values (20, 'mno' );

1 row inserted

SQL> select id,val from idtable;

ID VAL

---------- --------------------

10 abc

10 abc

10 def

10 def

20 ghi

20 jkl

20 mno

20 mno

8 rows selected

SQL> commit ;

Commit complete

SQL>

SQL> SELECT ID, WMSYS.WM_CONCAT(VAL) AS ENAMES

2 FROM IDTABLE

3 GROUP BY ID;

ID ENAMES

---------- --------------------------------------------------------------------------------

10 abc,abc,def,def

20 ghi,jkl,mno,mno

SQL>

SQL> SELECT ID, WMSYS.WM_CONCAT( DISTINCT VAL) AS ENAMES

2 FROM IDTABLE

3 GROUP BY ID

4 ORDER BY ID;

ID ENAMES

---------- --------------------------------------------------------------------------------

10 abc,def

20 ghi,jkl,mno

SQL>

SQL> SELECT ID, VAL, WMSYS.WM_CONCAT(VAL) OVER(PARTITION BY ID) AS ENAMES

2 FROM IDTABLE

3 ORDER BY ID;

ID VAL ENAMES

---------- -------------------- --------------------------------------------------------------------------------

10 abc abc,abc,def,def

10 abc abc,abc,def,def

10 def abc,abc,def,def

10 def abc,abc,def,def

20 ghi ghi,jkl,mno,mno

20 jkl ghi,jkl,mno,mno

20 mno ghi,jkl,mno,mno

20 mno ghi,jkl,mno,mno

8 rows selected

SQL>

SQL> SELECT ID, VAL, WMSYS.WM_CONCAT(VAL) OVER( ORDER BY ID, VAL) AS ENAMES

2 FROM IDTABLE

3 ORDER BY ID;

ID VAL ENAMES

---------- -------------------- --------------------------------------------------------------------------------

10 abc abc,abc

10 abc abc,abc

10 def abc,abc,def,def

10 def abc,abc,def,def

20 ghi abc,abc,def,def,ghi

20 jkl abc,abc,def,def,ghi,jkl

20 mno abc,abc,def,def,ghi,jkl,mno,mno

20 mno abc,abc,def,def,ghi,jkl,mno,mno

8 rows selected

 

 

 

注：用了这个函数后，感觉由行变列，是以那种String的形式，所以此时，如果数据库中的字段不是varchar的，用这个函数可能会出现类型映射的转换异常。

 

eg：本人用的mybatis，数据库中一字段time的类型为timestamp,对它用了这个函数，在数据库中SQL可以正常运行，但是在tomcat中运行程序是出现：

Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]这种异常，后经查看发现是SQL语句问题，这个字段不用此函数，程序运行无误。