行转列:把表中特定列的数据去重后做为列名;
列转行:可以说是行转列的反转,把表中特定列做为每一行数据对应列“CNAME”的值;
实例脚本:
DROP TABLE IF EXISTS `TabName`;
CREATE TABLE `TabName`
(
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(20) DEFAULT NULL,
`Date` date DEFAULT NULL,
`Scount` int(11) DEFAULT NULL,
PRIMARY KEY (`Id`)
)
ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
INSERT INTO `TabName` VALUES ('1','小说','2013-09-01','10000');
INSERT INTO `TabName` VALUES ('2','微信','2013-09-01','20000');
INSERT INTO `TabName` VALUES ('3','小说','2013-09-02','30000');
INSERT INTO `TabName` VALUES ('4','微信','2013-09-02','35000');
INSERT INTO `TabName` VALUES ('5','小说','2013-09-03','31000');
INSERT INTO `TabName` VALUES ('6','微信','2013-09-03','36000');
INSERT INTO `TabName` VALUES ('7','小说','2013-09-04','35000');
INSERT INTO `TabName` VALUES ('8','微信','2013-09-04','38000');

-- ------------------------ -- 列转行统计数据 -- ------------------------ SELECT Date , MAX ( CASE NAME WHEN '小说' THEN Scount ELSE 0 END ) 小说, MAX ( CASE NAME WHEN '微信' THEN Scount ELSE 0 END ) 微信 FROM TabName GROUP BY Date <br><br> |

-- ------------------------ -- 行转列统计数据 -- ------------------------ |

<br> select Date , group_concat( NAME , '总量:' ,Scount) as b_str from TabName group by Date |

select Date , NAME , group_concat( NAME , '总量:' ,Scount) as b_str from TabName group by Date , NAME |