mysql 多行变一行　若行与列转换

最新推荐文章于 2024-04-15 20:53:22 发布

水草会武

最新推荐文章于 2024-04-15 20:53:22 发布

阅读量1.8k

点赞数

文章标签： mysql class insert null table

本文链接：https://blog.csdn.net/shuicaohui5/article/details/3084127

版权

DateClass Class Count
2004-02 AU 32
2004-02 VAIO 56
2004-02 PB 77
2004-02 TV 89
2004-03 AU 38
2004-03 VAIO 99
2004-03 PB 32
2004-03 TV 87

要将查询结果变为

DateClass AU VAIO PB TV
2004-02 32 56 77 89
2004-03 38 99 32 87

建表：

CREATE TABLE `test` (
`dataClass` varchar(25) DEFAULT NULL,
`class` varchar(25) DEFAULT NULL,
`count` varchar(25) DEFAULT NULL
);

-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `test` VALUES ('2004-02', 'AU', '32');
INSERT INTO `test` VALUES ('2004-02', 'VAIO', '56');
INSERT INTO `test` VALUES ('2004-03', 'AU', '38');
INSERT INTO `test` VALUES ('2004-03', 'PB', '55');

ＳＱＬ实现：

select   DataClass   ,
                max(case   when   class   =   'AU'   then   Count   else   0   end)   as   AU,
                max(case   when   class   =   'VAIO'   then   Count   else   0   end)   as   VAIO,
                max(case   when   class   =   'PB'   then   Count   else   0   end)   as   PB,
                max(case   when   class   =   'TV'   then   Count   else   0   end)   as   TV
from   test
group   by   DataClass;

结果：

+-----------+----+------+----+----+
| DataClass | AU | VAIO | PB | TV |
+-----------+----+------+----+----+
| 2004-02 | 32 | 56 | 0 | 0 |
| 2004-03 | 38 | 0 | 55 | 0 |
+-----------+----+------+----+----+
2 rows in set