CREATE TABLE `t_dictionary` (
? `id` varchar(32) NOT NULL,
? `name` varchar(50) default NULL,
? `context` varchar(200) default NULL,
? `type` char(2) default NULL,
? `state` char(2) default NULL,
? `deleteFlag` char(2) default NULL,
? `sort` int(11) default NULL,
? PRIMARY KEY? (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='字典表';

CREATE TABLE `t_substituteleaving_new_leave` (
? `id` varchar(32) NOT NULL,
? `beginDate` datetime default NULL,
? `endDate` datetime default NULL,
? `days` varchar(10) default NULL,
? `dictionaryId` varchar(32) default NULL,
? `description` varchar(200) default NULL,
? `opinion` varchar(200) default NULL,
? `rejected` varchar(200) default NULL,
? `userId` varchar(32) default NULL,
? `userName` varchar(50) default NULL,
? `moneyFlag` char(2) default NULL,
? `settlementFlag` char(2) default NULL,
? `deleteFlag` char(2) default NULL,
? `state` char(2) default NULL,
? `createDate` datetime default NULL,
? `AUDITID` varchar(32) default NULL,
? `AUDITDATE` datetime default NULL,
? PRIMARY KEY? (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='请假信息表';

?

select? l.userName,
???? sum(if(name='病假',l.days,0) ) AS 病假,
???? sum(if(name='事假',l.days,0)) AS 事假,
???? sum(if(name='婚假',l.days,0)) AS 婚假,
???? sum(if(name='产假',l.days,0)) AS 产假,
???? sum(if(name='丧假',l.days,0)) AS 丧假,
???? sum(if(name='公假',l.days,0)) AS 公假,
???? sum(if(name='其他',l.days,0)) AS 其他,
???? SUM(l.days) AS TOTAL
???? from t_leave l left join? t_dictionary d on d.id=l.dictionaryId group by l.userName