面试-Sql专题-3-行转列案例汇总

行转列,笔试中经常出现,主要就是case简单函数和case搜索函数的使用,下面给大家几个案例

#案例1
##数据

CREATE TABLE `t_score` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `course` varchar(20) DEFAULT NULL,
  `score` float DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into t_score(name, course, score)  values
("张三", "数学", 34),
("张三", "语文", 58),
("张三", "英语", 58),
("李四", "数学", 45),
("李四", "语文", 87),
("李四", "英语", 45),
("王五", "数学", 76),
("王五", "语文", 34),
("王五", "英语", 89);

##数据效果

###1. 行转列SQL 语法一:简单case函数
需要效果

sql语句

SELECT name 姓名,
    MAX(CASE course WHEN '数学' THEN score ELSE 0 END ) 数学,
    MAX(CASE course WHEN '语文' THEN score ELSE 0 END ) 语文,
    MAX(CASE course WHEN '英语' THEN score ELSE 0 END ) 英语
FROM t_score
GROUP BY name;

###2. 行转列SQL 语法二:case搜索函数,这种语法后面可以带复杂逻辑,推荐使用

这个案例,还看不出case 搜索函数更强大的地方,只能看到另外一种写法

需要效果

sql语句

SELECT name 姓名,
    MAX(CASE  WHEN course='数学' THEN score ELSE 0 END ) 数学,
    MAX(CASE  WHEN course='语文' THEN score ELSE 0 END ) 语文,
    MAX(CASE  WHEN course='英语' THEN score ELSE 0 END ) 英语
FROM t_score
GROUP BY name;

###3. 带总分和平均分
需要效果

sql语句

select name 姓名,
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 英语,
SUM(score)总分,ROUND(avg(score)) 平均分 from t_score
group by name order by 总分;

###4. case when 带between and 函数
课程 不及格（0-59）个 良（60-80）个 优（81-100）个
需要效果

sql语句

	 select course 科目,
	 sum(case when score between 0 and 59 then 1 else 0 end) as 不及格,
	 sum(case when score between 60 and 80 then 1 else 0 end) as 良,
	 sum(case when score between 81 and 100 then 1 else 0 end) as 优秀 
	 from t_score
	 group by course;

案例2

##数据准备

CREATE TABLE `t_ke` (
  `id` int(10) NOT NULL AUTO_INCREMENT comment '',
  `teacherno` varchar(20) DEFAULT NULL comment '教室号',
  `weekno` varchar(20) DEFAULT NULL comment '星期号',
  `hasscore` varchar(10) DEFAULT '0' comment '是否有课',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `mianshi_5`.`t_ke` (`id`, `teacherno`, `weekno`, `hasscore`) VALUES (NULL, '1', '2', '有');
INSERT INTO `mianshi_5`.`t_ke` (`id`, `teacherno`, `weekno`, `hasscore`) VALUES (NULL, '1', '3', '有');
INSERT INTO `mianshi_5`.`t_ke` (`id`, `teacherno`, `weekno`, `hasscore`) VALUES (NULL, '2', '1', '有');
INSERT INTO `mianshi_5`.`t_ke` (`id`, `teacherno`, `weekno`, `hasscore`) VALUES (NULL, '3', '2', '有');
INSERT INTO `mianshi_5`.`t_ke` (`id`, `teacherno`, `weekno`, `hasscore`) VALUES (NULL, '1', '2', '有');
INSERT INTO `mianshi_5`.`t_ke` (`id`, `teacherno`, `weekno`, `hasscore`) VALUES (NULL, '2', '2', '无');

select * from t_ke;

##数据查看

###1. 一句sql,得到如下结果
需要效果

sql语句

SELECT teacherno 教师号 ,
    SUM(CASE  WHEN weekno='1' and hasscore='有' THEN 1 ELSE NULL END ) 星期一,
    SUM(CASE  WHEN weekno='2' and hasscore='有' THEN 1 ELSE NULL END ) 星期二,
    SUM(CASE  WHEN weekno='3' and hasscore='有' THEN 1 ELSE NULL END ) 星期三
FROM t_ke
GROUP BY teacherno; 

#案例3(了解)
##数据准备

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  * from TabName ;

##数据查看

###1. group_concat的使用-1
效果

SELECT
	Date,
	group_concat(NAME, '总量:', Scount) AS b_str
FROM
	TabName
GROUP BY
	Date;

###2. group_concat的使用-2
效果

SELECT
	Date,
	NAME,
	group_concat(NAME, '总量:', Scount) AS b_str
FROM
	TabName
GROUP BY
	Date,
	NAME