行转列:把表中特定列的数据去重后做为列名;
列转行:可以说是行转列的反转,把表中特定列做为每一行数据对应列“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
|