mysql 统计金额排序_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:=@rownum+1 xh,a.name,a.val,

if(@lastVal=a.val,@rank,@rank:=@rownum)pm,@lastVal:=a.val

from 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;

-- 行转列，方式1

select 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;

-- 行转列，方式2

select 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;

结果图片

标签：