mysql

Mysql 列转行统计查询 、行转列统计查询 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- ----------------------------  
-- Table structure for `TabName`  
-- ----------------------------  
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;  
   
-- ----------------------------  
-- Records of TabName  
-- ----------------------------  
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  * from TabName ;  

1
2
3
4
5
6
7
8
-- ------------------------  
-- 列转行统计数据  
-- ------------------------  
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  

1
2
3
4
5
6
-- ------------------------  
-- 行转列统计数据  
-- ------------------------  
 select  
    Date, group_concat(NAME,'总量:',Scount) as b_str from   TabName  
 group by Date  

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