create schema if not exists temp;
use temp;

--
--工作类型表
--
create table tbl_work_type (
    `type` varchar(16) not null,
    `description` varchar(16) not null,
    primary key(`type`)
);
insert into tbl_work_type(type, description) values('A', 'A类工作时间');
insert into tbl_work_type(type, description) values('B', 'B类工作时间');
insert into tbl_work_type(type, description) values('C', 'C类工作时间');
--
--工作记录表
--
create table tbl_work_schedule (
    `id` varchar(16) not null,
    `date` date not null,
    `type` varchar(16) not null,
    primary key(`id`, `date`),
    constraint `fk_schedule_1` foreign key(`type`) references `temp`.`tbl_work_type`(`type`)
);
insert into tbl_work_schedule(id, date, type) values('甲', '2016-06-01', 'C');
insert into tbl_work_schedule(id, date, type) values('甲', '2016-06-02', 'A');
insert into tbl_work_schedule(id, date, type) values('甲', '2016-06-03', 'B');
insert into tbl_work_schedule(id, date, type) values('甲', '2016-06-04', 'A');
insert into tbl_work_schedule(id, date, type) values('甲', '2016-06-05', 'A');
insert into tbl_work_schedule(id, date, type) values('乙', '2016-06-01', 'C');
insert into tbl_work_schedule(id, date, type) values('乙', '2016-06-02', 'B');
insert into tbl_work_schedule(id, date, type) values('乙', '2016-06-03', 'A');
insert into tbl_work_schedule(id, date, type) values('乙', '2016-06-04', 'C');
insert into tbl_work_schedule(id, date, type) values('乙', '2016-06-05', 'B');

现在要通过sql语句实现这样的效果

create table result (
    id varchar(16) not null,
    A int not null,    --统计A类工作天数
    B int not null,    --统计B类工作天数
    C int not null     --统计C类工作天数
);
insert into result(id, A, B, C) values('甲', 3, 1, 1);
insert into result(id, A, B, C) values('乙', 1, 2, 2);
select * from result;

需要注意的是，type表是可以增加种类的，比如新的类型D。那么result就要多一列D，数据为0.

脑袋转不过来了，帮帮忙！

[此贴子已经被作者于2016-6-3 17:27编辑过]

多谢指点

最后的结果做出来了，sum if 真是巧妙啊

set @sql = (select group_concat('sum(if(type = ''', type, '''', ', count, 0)) as ', type) from tbl_work_type);
set @sql = concat('select id, ', @sql, ' from (select id, type, count(*) as count from tbl_work_schedule group by id, type) a group by id');
prepare stmt from @sql;
execute stmt;

[此贴子已经被作者于2016-6-7 10:23编辑过]