oracle和mysql的行列轉換

rainbowbridg發表於2010-07-02

CREATE TABLE t(
class1 VARCHAR2(2 BYTE),
calldate DATE,
callcount INTEGER
);

INSERT INTO t(class1, calldate, callcount)
VALUES ('1', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 40);

INSERT INTO t(class1, calldate, callcount)
VALUES ('1', TO_DATE ('08/07/2005', 'MM/DD/YYYY'), 6);

INSERT INTO t(class1, calldate, callcount)
VALUES ('2', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 77);

INSERT INTO t(class1, calldate, callcount)
VALUES ('3', TO_DATE ('08/09/2005', 'MM/DD/YYYY'), 33);

INSERT INTO t(class1, calldate, callcount)
VALUES ('3', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 9);

INSERT INTO t(class1, calldate, callcount)
VALUES ('3', TO_DATE ('08/07/2005', 'MM/DD/YYYY'), 21);

COMMIT ;

oracle:

WMSYS.WM_CONCAT

SQL> select class1,wmsys.wm_concat(callcount) from t group by class1;

CLASS1 WMSYS.WM_CONCAT(CALLCOUNT)
------ --------------------------------------------------------------------------------
1 40,6
2 77
3 33,9,21

mysql :

group_concat

select class1,group_concat(callcount) from t group class1;

注意:group_concat(callcount)可能被截斷,因為有個引數規定了這個結果的長度,如果很長,需要修改這個引數!

ref:

[@more@]

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7916042/viewspace-1034843/,如需轉載,請註明出處,否則將追究法律責任。

相關文章