mysql交叉表的构造知识_mysql交叉表，行列动态转换

http://blog.csdn.net/ACMAIN_CHM/article/details/4283943

create table aaa(  id int primary key,  c1 char(2),  c2 char(2),  c3 int );

insert into aaa values (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);

mysql---- 中运行命令：

SET @EE="";

SELECT @EE:=CONCAT(@EE,"SUM(IF(C2='",C2,"'",",C3,0)) AS ",C2,",") FROM (SELECT DISTINCT C2 FROM aaa) A;

SET @QQ=CONCAT("SELECT ifnull(c1,'total'),",LEFT(@EE,LENGTH(@EE)-1)," ,SUM(C3) AS TOTAL FROM aaa GROUP BY C1 WITH ROLLUP");

PREPARE stmt2 FROM @QQ;

EXECUTE stmt2;

keyname keyvalue表

SELECT k.KEY_NAME, v.KEY_VALUE, k.SHOW_ORDER

FROM DYNA_PROCESS_KEY k, DYNA_PROCESS_VALUE v, ProcessInstanceInfo i

WHERE v.KEY_ID = k.ID

AND v.PROCESS_INSTANCE_ID = i.ID

AND i.processId LIKE "defaultPackage%"

create view aaa as SELECT k.KEY_NAME as c1, v.KEY_VALUE as c2, k.SHOW_ORDER as c3,v.PROCESS_INSTANCE_ID as instanceID

FROM DYNA_PROCESS_KEY k, DYNA_PROCESS_VALUE v, ProcessInstanceInfo i

WHERE v.KEY_ID = k.ID

AND v.PROCESS_INSTANCE_ID = i.ID

AND i.processId LIKE "defaultPackage.test@%"

列转行

SET @EE="";

SELECT @EE:=CONCAT(@EE,"SUM(IF(c2='",c2,"'",",c3,0)) AS ",if(c2!='',c2,'wuDingYi'),",") FROM (SELECT DISTINCT c2 FROM aaa) A;

SET @QQ="";

SET @QQ=CONCAT("SELECT ifnull(c1,'total'),",@EE," SUM(c3) AS TOTAL FROM aaa GROUP BY c1 WITH ROLLUP");

PREPARE stmt2 FROM @QQ;

EXECUTE stmt2;

SELECT max(IF(c1='姓名',c2,'wuDingYi')) AS 姓名,max(IF(c1='请假日期起',c2,0)) AS 请假日期起,max(IF(c1='请假日期止',c2,0)) AS 请假日期止,max(IF(c1='备注',c2,0)) AS 备注 FROM aaa  group by instanceID ;

计算请假总时间

drop view bbb;

create view bbb as SELECT max(IF(c1='姓名',c2,'wuDingYi')) AS 姓名,max(IF(c1='请假日期起',c2,0)) AS 请假日期起,max(IF(c1='请假日期止',c2,0)) AS 请假日期止,max(IF(c1='备注',c2,0)) AS 备注 FROM aaa  group by instanceID ;

select * from bbb;

select 姓名,sum(datediff(请假日期止,请假日期起)) from bbb group by 姓名;