mysql 纵向 数据 展示横行字段

mysql 纵向 数据 显示横行字段

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

喜欢 (0)赏

[搞代码]

分享 (0)