MySQL, sort, stats row to column

Table

-- ----------------------------
--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) DEF Ault 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 '); Inser T into ' a ' values (' x ', ' A ', ' 4 '); insert INTO ' a ' values (' y ', ' B ', ' 5 '); insert INTO ' a ' values (' y ', ' C ', ' 5 '); Insert INT  O ' 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 ' VALU ES (' 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 statements

--serial number, ranking 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;--statistics for Row select Type,name,sum (val) from a GROUP by a.type,a.name;--statistics for column 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;--row to column, way 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;--Row to column, way 2select t.type,sum (if (t.name= ' a ', t.sl,0)) Suma,su M (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; 

Result picture

MySQL, sort, stats row to column