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

分享到：

2012-05-15 14:12

浏览 2048

分类:数据库

评论