6,108
社区成员




我自己建了一个table,包含了四个字段type,install,maintan,examine,经过sql处理后,生成四个字段person,install1,maintan1,examine1分别代表每个工人做的安装,维修,检查的次数
select Temp.person,count(Temp.install) as install1,count(Temp.maintan) as maintan1, count(Temp.examine) examine1 from
(select case when install is not Null then install
when maintan is not Null then maintan
when examine is not Null then examine end person,type,install,maintan,examine
from t_service) Temp group by person
create table temp(
id int auto_increment primary key,
type varchar(10),
install_man varchar(10),
check_man varchar(10),
fix_man varchar(10)
)
insert into temp(type,install_man,check_man,fix_man) values('fix',null,null,'zhangsan');
insert into temp(type,install_man,check_man,fix_man) values('install','wangwu',null,null);
insert into temp(type,install_man,check_man,fix_man) values('check',null,'zhangsan',null);
insert into temp(type,install_man,check_man,fix_man) values('install','lisi',null,null);
--原始表:
+----+---------+-------------+-----------+----------+
| id | type | install_man | check_man | fix_man |
+----+---------+-------------+-----------+----------+
| 1 | fix | NULL | NULL | zhangsan |
| 2 | install | wangwu | NULL | NULL |
| 3 | check | NULL | zhangsan | NULL |
| 4 | install | lisi | NULL | NULL |
+----+---------+-------------+-----------+----------+
--tempTable:(1)是过渡阶段,(2)是最终的tempTable.
select type,
case when install_man is not null then install_man
when check_man is not null then check_man
when fix_man is not null then fix_man
end as man_name
from temp;
+---------+----------+
| type | man_name |
+---------+----------+
| fix | zhangsan |(1)
| install | wangwu |
| check | zhangsan |
| install | lisi |
+---------+----------+
select type,
case when install_man is not null then install_man
when check_man is not null then check_man
when fix_man is not null then fix_man
end as man_name,count(*) as times
from temp group by man_name,type;
+---------+----------+-------+
| type | man_name | times |
+---------+----------+-------+
| install | lisi | 1 |
| install | wangwu | 1 |(2)
| check | zhangsan | 1 |
| fix | zhangsan | 1 |
+---------+----------+-------+
--最后的结果:(1)是中间过渡阶段(2)是最后的结果
select temptable.man_name as serviceman,
case temptable.type when 'install' then temptable.times else 0 end as install,
case temptable.type when 'fix' then temptable.times else 0 end as fix,
case temptable.type when 'check' then temptable.times else 0 end as 'check'
from
(
select type,
case when install_man is not null then install_man
when check_man is not null then check_man
when fix_man is not null then fix_man
end as man_name,count(*) as times
from temp group by man_name,type
)as temptable
+------------+---------+------+-------+
| serviceman | install | fix | check |
+------------+---------+------+-------+
| lisi | 1 | 0 | 0 |
| wangwu | 1 | 0 | 0 |(1)
| zhangsan | 0 | 0 | 1 |
| zhangsan | 0 | 1 | 0 |
+------------+---------+------+-------+
select temptable.man_name as serviceman,
max(case temptable.type when 'install' then temptable.times else 0 end) as install,
max(case temptable.type when 'fix' then temptable.times else 0 end) as fix,
max(case temptable.type when 'check' then temptable.times else 0 end) as 'check'
from
(
select type,
case when install_man is not null then install_man
when check_man is not null then check_man
when fix_man is not null then fix_man
end as man_name,count(*) as times
from temp group by man_name,type
)as temptable group by serviceman;
+------------+---------+------+-------+
| serviceman | install | fix | check |
+------------+---------+------+-------+
| lisi | 1 | 0 | 0 |(2)
| wangwu | 1 | 0 | 0 |
| zhangsan | 0 | 1 | 1 |
+------------+---------+------+-------+
--楼主的表述不清楚呀。。。
create table temp(
id int auto_increment primary key,
type varchar(10),
install_man varchar(10),
check_man varchar(10),
fix_man varchar(10)
)
insert into temp(type,install_man,check_man,fix_man) values('fix',null,null,'zhangsan');
insert into temp(type,install_man,check_man,fix_man) values('install','wangwu',null,null);
insert into temp(type,install_man,check_man,fix_man) values('check',null,'zhangsan',null);
insert into temp(type,install_man,check_man,fix_man) values('install','lisi',null,null);
原始表:
+----+---------+-------------+-----------+----------+
| id | type | install_man | check_man | fix_man |
+----+---------+-------------+-----------+----------+
| 1 | fix | NULL | NULL | zhangsan |
| 2 | install | wangwu | NULL | NULL |
| 3 | check | NULL | zhangsan | NULL |
| 4 | install | lisi | NULL | NULL |
+----+---------+-------------+-----------+----------+
tempTable:(1)是过渡阶段,(2)是最终的tempTable.
select type,
case when install_man is not null then install_man
when check_man is not null then check_man
when fix_man is not null then fix_man
end as man_name as times
from temp;
+---------+----------+
| type | man |
+---------+----------+
| fix | zhangsan |(1)
| install | wangwu |
| check | zhangsan |
| install | lisi |
+---------+----------+
select type,
case when install_man is not null then install_man
when check_man is not null then check_man
when fix_man is not null then fix_man
end as man_name,count(*) as times
from temp group by man_name,type;
+---------+----------+-------+
| type | man_name | times |
+---------+----------+-------+
| install | lisi | 1 |
| install | wangwu | 1 |(2)
| check | zhangsan | 1 |
| fix | zhangsan | 1 |
+---------+----------+-------+
最后的结果:(1)是中间过渡阶段(2)是最后的结果
select temptable.man_name as serviceman,
case temptable.type when 'install' then temptable.times else 0 end as install,
case temptable.type when 'fix' then temptable.times else 0 end as fix,
case temptable.type when 'check' then temptable.times else 0 end as 'check'
from
(
select type,
case when install_man is not null then install_man
when check_man is not null then check_man
when fix_man is not null then fix_man
end as man_name,count(*) as times
from temp group by man_name,type
)as temptable
+------------+---------+------+-------+
| serviceman | install | fix | check |
+------------+---------+------+-------+
| lisi | 1 | 0 | 0 |
| wangwu | 1 | 0 | 0 |(1)
| zhangsan | 0 | 0 | 1 |
| zhangsan | 0 | 1 | 0 |
+------------+---------+------+-------+
select temptable.man_name as serviceman,
max(case temptable.type when 'install' then temptable.times else 0 end) as install,
max(case temptable.type when 'fix' then temptable.times else 0 end) as fix,
max(case temptable.type when 'check' then temptable.times else 0 end) as 'check'
from
(
select type,
case when install_man is not null then install_man
when check_man is not null then check_man
when fix_man is not null then fix_man
end as man_name,count(*) as times
from temp group by man_name,type
)as temptable group by serviceman;
+------------+---------+------+-------+
| serviceman | install | fix | check |
+------------+---------+------+-------+
| lisi | 1 | 0 | 0 |(2)
| wangwu | 1 | 0 | 0 |
| zhangsan | 0 | 1 | 1 |
+------------+---------+------+-------+