sql行转列案例

原创于 2024-09-29 14:58:30 发布 · 247 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

列转行原文强推:https://blog.youkuaiyun.com/qq_38747892/article/details/122946152

数据准备:

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uname` varchar(255) DEFAULT NULL,
  `course` varchar(255) DEFAULT NULL,
  `grade` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '张三', 'Java', '70');
INSERT INTO `user` VALUES ('2', '张三', 'Python', '80');
INSERT INTO `user` VALUES ('3', 'lisi', 'Java', '90');
INSERT INTO `user` VALUES ('4', 'lisi', 'C', '40');
INSERT INTO `user` VALUES ('5', 'lisi', 'Vue', '90');

select uname,
	sum(case when course='Java' then grade end) as Java,
	sum(case when course='python' then grade end) as Python,
	sum(case when course='Vue' then grade end) as Vue,
	sum(case when course='C' then grade end) as C
from user group by uname;

在这里插入图片描述

带汇总

select uname,
	sum(IFNULL(grade,0)) as Total,
	sum(IFNULL(case when course='Java' then grade end,0)) as Java,
	sum(IFNULL(case when course='python' then grade end,0)) as Python,
	sum(IFNULL(case when course='Vue' then grade end,0)) as Vue,
	sum(IFNULL(case when course='C' then grade end,0)) as C
from user group by uname;

在这里插入图片描述

部分组合相加为一个类型

select uname,
(
	sum(IFNULL(case when course='Java' then grade end,0)) +
	sum(IFNULL(case when course='python' then grade end,0)) +
	sum(IFNULL(case when course='C' then grade end,0))
) backend,
sum(IFNULL(case when course='Vue' then grade end,0)) as frontend
from user group by uname;