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