MySQL横竖表转换

一时失言乱红尘 2022-09-03 01:19 11阅读 0赞

# 竖表变横表 #

CREATE TABLE `student` (
      `id` int(11) NOT NULL,
      `sub` varchar(255) DEFAULT NULL,
      `score` int(11) DEFAULT NULL,
      `userid` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of student
    -- ----------------------------
    INSERT INTO `student` VALUES ('1', '语文', '98', '1');
    INSERT INTO `student` VALUES ('2', '数学', '99', '1');
    INSERT INTO `student` VALUES ('3', '英语', '66', '1');
    INSERT INTO `student` VALUES ('4', '语文', '198', '11');
    INSERT INTO `student` VALUES ('5', '数学', '991', '11');
    INSERT INTO `student` VALUES ('6', '英语', '661', '11');

select userid,
    max(case sub when "语文" then score else 0 end) as  "语文",
    max(case sub when "数学" then score else 0 end) as  "数学",
    max(case sub when "英语" then score else 0 end) as "英语"
    from student GROUP BY userid;

![在这里插入图片描述][6b2c61212918497a87163d6a05f1be11.png]

# 横表变竖表 #

CREATE TABLE `student` (
      `id` int(11) NOT NULL,
      `english` int(11) DEFAULT NULL,
      `math` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of student
    -- ----------------------------
    INSERT INTO `student` VALUES ('1', '98', '89');
    INSERT INTO `student` VALUES ('2', '99', '99');
    INSERT INTO `student` VALUES ('3', '66', '99');

select * from (
    select id,"英语" as subject,english as score from student
    UNION
    select id,"数学" as subject,math as score from student
    ) tb ORDER BY id;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3MTcxMzUz_size_16_color_FFFFFF_t_70]

# 参考 #

https://blog.csdn.net/yuxin6866/article/details/53107286

[6b2c61212918497a87163d6a05f1be11.png]: /images/20220829/555ffc57670f49c2bf1dfe328cc50fd2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3MTcxMzUz_size_16_color_FFFFFF_t_70]: /images/20220829/3b54f98e086d4548bb20d8a4444bfcf4.png