mysql 列传行 行转列 个人总结

表结构数据转载自:http://stunizhengjia.iteye.com/blog/2032861

数据表：

sql:

CREATE TABLE `t_shcool` (  
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',  
  `userName` varchar(50) DEFAULT NULL COMMENT '姓名',  
  `course` varchar(50) DEFAULT NULL COMMENT '科目',  
  `score` int(20) DEFAULT NULL COMMENT '成绩',  
  PRIMARY KEY (`id`)  
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;  

insert  into `t_shcool`  
(`id`,`userName`,`course`,`score`)   
values   
(1,'张三','语文',66),  
(2,'张三','数学',77),  
(3,'张三','英文',88),  
(4,'李四','语文',99),  
(5,'李四','数学',100),  
(6,'李四','英文',55),  
(7,'王五','语文',44),  
(8,'王五','数学',33),  
(9,'马六','英文',22),  
(10,'马六','语文',11); 

表预览：

列传行 

SELECT   
userName AS '姓名',  
SUM(CASE  WHEN course='语文' THEN score ELSE 0 END) AS '语文',  
SUM(CASE  WHEN course='数学' THEN score ELSE 0 END) AS '数学',  
SUM(CASE  WHEN course='英文' THEN score ELSE 0 END) AS '英文'  
FROM t_shcool GROUP BY userName  

结果

小结:

course='语文' 表示当course列值为语文时 (列传行) 

THEN score 表示匹配对应的值,这边是对应score列的值 (设置值) 

ELSE 0 END 表示其他情况 这边的0等于null 表示不用

AS''表示新列的名字 (行名)

SUM表示和也可以用MAX表示最大值(如张三有2个语文成绩的时候就匹配最大的如果是SUM则是相加)

注意使用group by  不然只会显示一行数据

行转列

sql:

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, 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  

效果