急求个SQL语句！解决思路

急急急！求个SQL语句！
小弟想求出每个工程师的安装产品次数，维修产品次数，验收产品次数。

所用表字段:服务类型（包括值：安装、维修、验收），安装工程师，维修工程师，验收工程师。
安装工程师、维修工程师、验收工程师这三个字段在每条记录当中，只有其中一个字段有值。

小弟写了一个SQL语句结果为：

怎样实现这种效果：

------解决方案--------------------
楼主，图太小，没法看清楚，有另上图吧！
------解决方案--------------------
哪个能看清罗？

------解决方案--------------------
我眼神不好可能！
------解决方案--------------------
贴建表及插入记录的SQL，及要求结果出来看看
------解决方案--------------------
楼主，图太小，没法看清楚，有另上图吧！

贴建表及插入记录的SQL，及要求结果出来看看
------解决方案--------------------
看不清楚，重新发图！！
------解决方案--------------------
图片.楼主
------解决方案--------------------
select 服务工程师,sum(安装),sum(维修),sum(验收) from 表 GROUP BY 工程师
------解决方案--------------------

探讨

select 服务工程师,sum(安装),sum(维修),sum(验收) from 表 GROUP BY 工程师

------解决方案--------------------

探讨
select 服务工程师,sum(安装),sum(维修),sum(验收) from 表 GROUP BY 工程师

------解决方案--------------------

探讨
引用:
select 服务工程师,sum(安装),sum(维修),sum(验收) from 表 GROUP BY 工程师

你这个不对了
这个只是求行数
如果栏位里面有数值它也只是代表一行
报表不能这么做
suM()...group by原理是对的

------解决方案--------------------
好像没啥规律，lz给规律呀。
row_number() over()
------解决方案--------------------
我自己建了一个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
------解决方案--------------------

SQL code


--楼主的表述不清楚呀。。。
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 |
+------------+---------+------+-------+

急求个SQL语句！解决思路

相关推荐