mysql 行列动态转换的实现(列联表,交叉表)
（1）动态，适用于列不确定情况
createtabletable_name( idintprimarykey, col1char(2), col2char(2), col3int );
insertintotable_namevalues (1,'A1','B1',9), (2,'A2','B1',7), (3,'A3','B1',4), (4,'A4','B1',2), (5,'A1','B2',2), (6,'A2','B2',9), (7,'A3','B2',8), (8,'A4','B2',5), (9,'A1','B3',1), (10,'A2','B3',8), (11,'A3','B3',8), (12,'A4','B3',6), (13,'A1','B4',8), (14,'A2','B4',2), (15,'A3','B4',6), (16,'A4','B4',9), (17,'A1','B4',3), (18,'A2','B4',5), (19,'A3','B4',2), (20,'A4','B4',5);
select*fromtable_name; +----+------+------+------+ |id|col1|col2|col3| +----+------+------+------+ |1|A1|B1|9| |2|A2|B1|7| |3|A3|B1|4| |4|A4|B1|2| |5|A1|B2|2| |6|A2|B2|9| |7|A3|B2|8| |8|A4|B2|5| |9|A1|B3|1| |10|A2|B3|8| |11|A3|B3|8| |12|A4|B3|6| |13|A1|B4|8| |14|A2|B4|2| |15|A3|B4|6| |16|A4|B4|9| |17|A1|B4|3| |18|A2|B4|5| |19|A3|B4|2| |20|A4|B4|5| +----+------+------+------+
SET@EE='';
SELECT@EE:=CONCAT(@EE,'SUM(IF(col2=\'',col2,'\'',',col3,0))AS',col2,',')FROM(SELECTDISTINCTcol2FROMtable_name)A;
SET@QQ=CONCAT('SELECTifnull(col1,\'total\')AScolumnA,',LEFT(@EE,LENGTH(@EE)-1),',SUM(col3)ASTOTALFROMtable_nameGROUPBYcol1WITHROLLUP');
PREPAREstmt2FROM@QQ;
EXECUTEstmt2;
+---------+------+------+------+------+-------+ |columnA|B1|B2|B3|B4|TOTAL| +---------+------+------+------+------+-------+ |A1|9|2|1|11|23| |A2|7|9|8|7|31| |A3|4|8|8|8|28| |A4|2|5|6|14|27| |total|22|24|23|40|109| +---------+------+------+------+------+-------+
（2）第二个字段确定的情况下使用
SELECT IFNULL(col1,'total')AStotal, SUM(IF(col2='B1',col3,0))ASB1, SUM(IF(col2='B2',col3,0))ASB2, SUM(IF(col2='B3',col3,0))ASB3, SUM(IF(col2='B4',col3,0))ASB4, SUM(IF(col2='total',col3,0))AStotal FROM( SELECTcol1,IFNULL(col2,'total')AScol2,SUM(col3)AScol3 FROMtable_name GROUPBYcol1,col2 WITHROLLUP HAVINGcol1ISNOTNULL )ASA GROUPBYcol1 WITHROLLUP;
注：WITHROLLUP用于列上求和；SUM(IF(col2='total',col3,0))AStotal用于行上求和。
（3）第二个字段确定的情况下使用
selectifnull(col1,'total')AScol1, sum(if(col2='B1',col3,0))ASB1, sum(if(col2='B2',col3,0))ASB2, sum(if(col2='B3',col3,0))ASB3, sum(if(col2='B4',col3,0))ASB4,SUM(col3)ASTOTAL fromtable_name groupbycol1withrollup;
以上这篇mysql行列动态转换的实现(列联表,交叉表)就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持毛票票。