mysql纵表变横表 - 炫Q香蕉派

mysql纵表变横表

数据以流水存放

CREATE TABLE `tab` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `n` varchar(3) DEFAULT NULL,
  `t` varchar(1) DEFAULT NULL,
  `v` float DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `tab` VALUES ('1', '101', 'A', '1');
INSERT INTO `tab` VALUES ('2', '102', 'A', '2');
INSERT INTO `tab` VALUES ('3', '102', 'B', '3');
INSERT INTO `tab` VALUES ('4', '103', 'B', '4');

上SQL

select n,
sum(case t when 'A' then v else null end) as A,
sum(case t when 'B' then v else null end) as B
from tab group by n order by n

结果

n   A   B
101 1   
102 2   3
103     4

标签: none

添加新评论