mysql交表_MYSQL 交叉表

数据：

CREATE TABLE sitemvalue(

STCD INT ,

XMMC VARCHAR(20),

SValue DECIMAL(4,2) ,

PRIMARY KEY(stcd,xmmc)

)

INSERT INTO sitemvalue VALUES ('0001', 'ph', 7.0) ;

INSERT INTO sitemvalue VALUES ('0001', 'cod', 0.2) ;

INSERT INTO sitemvalue VALUES ('0001', 'do', 0.3) ;

INSERT INTO sitemvalue VALUES ('0001', 'wt', 1.1) ;

INSERT INTO sitemvalue VALUES ('0002', 'ph', 7.0) ;

INSERT INTO sitemvalue VALUES ('0002', 'do', 0.2) ;

INSERT INTO sitemvalue VALUES ('0002', 'wt', 0.2) ;

INSERT INTO sitemvalue VALUES ('0002', 'cod', 1.5) ;

不带TOTAL统计的：

SELECT IFNULL(STCD ,'total') AS STCD ,

SUM(IF(XMMC ='ph',SValue ,0)) AS 'ph',

SUM(IF(XMMC ='cod',SValue ,0)) AS 'cod',

SUM(IF(XMMC ='do',SValue ,0)) AS 'do',

SUM(IF(XMMC ='wt',SValue ,0)) AS 'wt'

FROM sitemvalue GROUP BY STCD

带TOTAL统计的：

SELECT IFNULL(STCD ,'total') AS STCD ,

SUM(IF(XMMC ='ph',SValue ,0)) AS 'ph',

SUM(IF(XMMC ='cod',SValue ,0)) AS 'cod',

SUM(IF(XMMC ='do',SValue ,0)) AS 'do',

SUM(IF(XMMC ='wt',SValue ,0)) AS 'wt'

FROM sitemvalue GROUP BY STCD

UNION

SELECT 'total' ,

SUM(IF(XMMC ='ph',SValue ,0)) AS 'ph',

SUM(IF(XMMC ='cod',SValue ,0)) AS 'cod',

SUM(IF(XMMC ='do',SValue ,0)) AS 'do',

SUM(IF(XMMC ='wt',SValue ,0)) AS 'wt'

FROM sitemvalue

带TOTAL统计的：

SELECT IFNULL(STCD ,'total') AS STCD ,

SUM(IF(XMMC ='ph',SValue ,0)) AS 'ph',

SUM(IF(XMMC ='cod',SValue ,0)) AS 'cod',

SUM(IF(XMMC ='do',SValue ,0)) AS 'do',

SUM(IF(XMMC ='wt',SValue ,0)) AS 'wt'

FROM sitemvalue GROUP BY STCD WITH ROLLUP