mysql表行列互换

来源：互联网发布：电子商务教学软件下载编辑：程序博客网时间：2024/06/05 06:15

1. 问题描述

查询下面数据库表数据

namecoursescore张三语文88张三数学92张三物理93李四语文79李四数学99李四物理94

查询结果如下：

name语文数学物理张三889293李四799994

2. 解决方法

测试数据：

-- ------------------------------ Table structure for `score`-- ----------------------------DROP TABLE IF EXISTS `score`;CREATE TABLE `score` (  `name` varchar(10) DEFAULT NULL,  `course` varchar(10) DEFAULT NULL,  `score` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of score-- ----------------------------INSERT INTO `score` VALUES ('张三', '语文', '88');INSERT INTO `score` VALUES ('张三', '数学', '92');INSERT INTO `score` VALUES ('张三', '物理', '93');INSERT INTO `score` VALUES ('李四', '语文', '79');INSERT INTO `score` VALUES ('李四', '数学', '99');INSERT INTO `score` VALUES ('李四', '物理', '94');

1. 使用IF()函数

SELECT name, SUM(IF(course = '语文',score,0)) as "语文",SUM(IF(course = '数学',score,0)) as "数学",SUM(IF(course = '物理',score,0)) as "物理"FROM score GROUP BY name;

2. 使用CASE WHEN

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 "物理"FROM score GROUP BY name;

阅读全文

0 0