Mysql 行列转置

来源：互联网发布：淘宝自动发卡软件编辑：程序博客网时间：2024/05/27 01:47

建表代码

/*Navicat MySQL Data TransferSource Server         : 115.28.24.231Source Server Version : 50621Source Host           : 115.28.24.231:3306Source Database       : testTarget Server Type    : MYSQLTarget Server Version : 50621File Encoding         : 65001Date: 2016-03-15 11:50:49*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for student-- ----------------------------DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (  `name` varchar(11) DEFAULT NULL,  `Math` int(11) DEFAULT NULL,  `English` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of student-- ----------------------------INSERT INTO `student` VALUES ('甲', '89', '78');INSERT INTO `student` VALUES ('乙', '77', '81');INSERT INTO `student` VALUES ('丙', '87', '98');

if语句使用：

SELECTIF (    name = 'ww',    '万',    '其他') ASVALUE,    math,    englishFROM    student

Case语句使用

SELECT    (        case username        WHEN 'ww' THEN '万'        ELSE '其他' END    ) ASVALUE,    math,    englishFROM    student

行列转置

SELECT    col2 AS '课程',    #甲的xx课程的成绩    sum(IF(NAME = '甲', col3, 0)) AS '甲',    #乙的xx课程的成绩    sum(IF(NAME = '乙', col3, 0)) AS '乙',    #丙的xx课程的成绩      sum(IF(NAME = '丙', col3, 0)) AS '丙'FROM    (        #形成name（甲乙丙），col2（math，english），col3（col2对应的成绩）        SELECT            NAME,            '数学' AS col2,            student.math AS col3        FROM            student        GROUP BY            NAME        UNION            SELECT                NAME,                '英语' AS col2,                student.english AS col3            FROM                student            GROUP BY                NAME    ) AS val #根据课程分组GROUP BY    col2

0 0