急急急！求个SQL语句！

[zsllin]

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

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



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


怎样实现这种效果：

[wwwwb]

贴建表及插入记录的SQL，及要求结果出来看看

• 打赏
• 举报

回复 赞

[LI8406]

哪个能看清罗？

• 打赏
• 举报

回复 赞

[熏衣草]

楼主，图太小，没法看清楚，有另上图吧！


贴建表及插入记录的SQL，及要求结果出来看看

• 打赏
• 举报

回复 赞

[chen668899163]

看不清楚，重新发图！！

• 打赏
• 举报

回复 赞

[mo_ying]

图片.楼主

• 打赏
• 举报

回复 赞

[Robert1988chan]

lz，看不清楚额。。。

• 打赏
• 举报

回复 赞

[wolfwu_kg5]

需求没看懂。。。看你的结果和需求的结果就是少了2条记录？

• 打赏
• 举报

回复 赞

[我是小数位]

呵呵，都是人才

• 打赏
• 举报

回复 赞

[尤卡里]

楼主。。。。怎么感觉有点乱

• 打赏
• 举报

回复 赞

[e8soft]

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

• 打赏
• 举报

回复 赞

[冷少爷]

[Quote=引用 12 楼 e8soft 的回复:]

select 服务工程师,sum(安装),sum(维修),sum(验收) from 表 GROUP BY 工程师
[/Quote]
这样是错误的，楼主的需要是求次数。

建议把字段值=0的update为null，再count(), count()会计算0，但不计算null

• 打赏
• 举报

回复 赞

[a29374963]

[Quote=引用 12 楼 e8soft 的回复:]
select 服务工程师,sum(安装),sum(维修),sum(验收) from 表 GROUP BY 工程师
[/Quote]

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

• 打赏
• 举报

回复 赞

[fisher-jie]

好像没啥规律，lz给规律呀。
row_number() over()

• 打赏
• 举报

回复 赞

[hoho0326077]

[Quote=引用 14 楼 a29374963 的回复:]
引用 12 楼 e8soft 的回复:
select 服务工程师,sum(安装),sum(维修),sum(验收) from 表 GROUP BY 工程师


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

看楼主贴出来的数据，不像是用group by 他好像只是提取每个人的第一条记录的
如果是那样的话，就很简单了

• 打赏
• 举报

回复 赞

[Jone_wan]

我自己建了一个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

• 打赏
• 举报

回复 赞

[bearfly1990]

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

• 打赏
• 举报

回复 赞

[bearfly1990]

CSDN不给力呀，想改自己的帖子都不行，漂亮又细心的同事指出了一些错误，现在fix了再发一下^_^
我用的方法中用到了一维表转二维表的方法，其实已有牛人解释过，在CSDN上http://topic.csdn.net/u/20081231/22/88809213-b3fa-4edc-a0a1-5a618b4ab336.html

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 |
+------------+---------+------+-------+

• 打赏
• 举报

回复 赞

[bearfly1990]

[Quote=引用 18 楼 jone_wan 的回复:]
我自己建了一个table，包含了四个字段type,install,maintan,examine,经过sql处理后，生成四个字段person,install1,maintan1,examine1分别代表每个工人做的安装，维修，检查的次数
[/Quote]

我自己建了一个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

看完楼上的才发现自己的方法真是太搓了，太麻烦了，顶起哈，神作，膜拜ing!

• 打赏
• 举报

回复 赞

[BI_expert]

这个sql没有LS写的那么复杂吧，自身觉得，只要取你想要的 where条件 判断即可

• 打赏
• 举报

回复 赞

[zsllin]

各位对不住了，因前些日子，有急事，一直没上网。

• 打赏
• 举报

回复 赞