MySQL，排序，統計行轉列

標籤：

表

-- ----------------------------
-- Table structure for a
-- ----------------------------

DROP TABLE IF EXISTS `a`;CREATE TABLE `a` (  `type` varchar(255) DEFAULT NULL,  `name` varchar(255) DEFAULT NULL,  `val` varchar(255) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 -- ----------------------------
-- Records of a
-- ----------------------------

INSERT INTO `a` VALUES (‘x‘, ‘A‘, ‘1‘);INSERT INTO `a` VALUES (‘x‘, ‘B‘, ‘2‘);INSERT INTO `a` VALUES (‘x‘, ‘C‘, ‘2‘);INSERT INTO `a` VALUES (‘x‘, ‘A‘, ‘4‘);INSERT INTO `a` VALUES (‘y‘, ‘B‘, ‘5‘);INSERT INTO `a` VALUES (‘y‘, ‘C‘, ‘5‘);INSERT INTO `a` VALUES (‘y‘, ‘D‘, ‘5‘);INSERT INTO `a` VALUES (‘z‘, ‘A‘, ‘8‘);INSERT INTO `a` VALUES (‘z‘, ‘B‘, ‘9‘);INSERT INTO `a` VALUES (‘x‘, ‘C‘, ‘1‘);INSERT INTO `a` VALUES (‘x‘, ‘D‘, ‘2‘);INSERT INTO `a` VALUES (‘y‘, ‘D‘, ‘3‘);INSERT INTO `a` VALUES (‘y‘, ‘D‘, ‘4‘);INSERT INTO `a` VALUES (‘z‘, ‘C‘, ‘5‘);INSERT INTO `a` VALUES (‘z‘, ‘C‘, ‘6‘);INSERT INTO `a` VALUES (‘z‘, ‘C‘, ‘2‘);INSERT INTO `a` VALUES (‘z‘, ‘A‘, ‘4‘);

 SQL語句

-- 序號、排名select @rownum:[email protected]+1 xh,a.name,a.val,if(@lastVal=a.val,@rank,@rank:[email protected])pm,@lastVal:=a.valfrom  a,(select @rownum:=0,@rank:=0,@lastVal:=null) b ORDER BY a.val;-- 統計為行select type,name,sum(val) from a GROUP BY a.type,a.name;-- 統計為行列select t.type,if(t.name=‘A‘,t.sl,0)A,if(t.name=‘B‘,t.sl,0)B,if(t.name=‘C‘,t.sl,0)C,if(t.name=‘D‘,t.sl,0)D from(select type,name,sum(val)sl from a GROUP BY a.type,a.name)t;-- 行轉列，方式1select t1.type,sum(A) sumA,sum(B) sumB,sum(C) sumC,sum(D) sumD from (select t.type,if(t.name=‘A‘,t.sl,0)A,if(t.name=‘B‘,t.sl,0)B,if(t.name=‘C‘,t.sl,0)C,if(t.name=‘D‘,t.sl,0)D from(select type,name,sum(val)sl from a GROUP BY a.type,a.name)t)t1 group by t1.type;-- 行轉列，方式2select t.type,sum(if(t.name=‘a‘,t.sl,0)) sumA,sum(if(t.name=‘b‘,t.sl,0)) sumB,sum(if(t.name=‘c‘,t.sl,0)) sumC,sum(if(t.name=‘d‘,t.sl,0)) sumD from(select type,name,sum(val)sl from a GROUP BY a.type,a.name)t group by t.type;

 結果圖片

MySQL，排序，統計行轉列