急急急!求个SQL语句!

zsllin 2011-12-22 02:51:35
小弟想求出每个工程师的安装产品次数,维修产品次数,验收产品次数。

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



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


怎样实现这种效果:
...全文
321 点赞 收藏 24
写回复
24 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
zsllin 2012-05-22
各位对不住了,因前些日子,有急事,一直没上网。
回复
BI_expert 2012-04-19
这个sql没有LS写的那么复杂吧,自身觉得,只要取你想要的 where条件 判断即可
回复
bearfly1990 2012-03-23
[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!
回复
bearfly1990 2012-03-23
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 2012-03-23

--楼主的表述不清楚呀。。。
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 |
+------------+---------+------+-------+
回复
Jone_wan 2012-03-23
我自己建了一个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
回复
hoho0326077 2012-03-21
[Quote=引用 14 楼 a29374963 的回复:]
引用 12 楼 e8soft 的回复:
select 服务工程师,sum(安装),sum(维修),sum(验收) from 表 GROUP BY 工程师


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

看楼主贴出来的数据,不像是用group by 他好像只是提取每个人的第一条记录的
如果是那样的话,就很简单了
回复
fisher-jie 2012-03-21
好像没啥规律,lz给规律呀。
row_number() over()
回复
a29374963 2012-03-16
[Quote=引用 12 楼 e8soft 的回复:]
select 服务工程师,sum(安装),sum(维修),sum(验收) from 表 GROUP BY 工程师
[/Quote]

你这个不对了
这个只是求行数
如果栏位里面有数值 它也只是代表一行
报表不能这么做
suM()...group by原理是对的
回复
冷少爷 2012-03-14
[Quote=引用 12 楼 e8soft 的回复:]

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

建议把字段值=0的update为null,再count(), count()会计算0,但不计算null
回复
e8soft 2012-03-09
select 服务工程师,sum(安装),sum(维修),sum(验收) from 表 GROUP BY 工程师
回复
尤卡里 2012-02-25
楼主。。。。怎么感觉有点乱
回复
我是小数位 2012-02-22
呵呵,都是人才
回复
wolfwu_kg5 2012-02-21
需求没看懂。。。看你的结果和需求的结果就是少了2条记录?
回复
Robert1988chan 2012-02-06
lz,看不清楚额。。。
回复
mo_ying 2012-02-04
图片.楼主
回复
chen668899163 2012-01-31
看不清楚,重新发图!!
回复
熏衣草 2012-01-29
楼主,图太小,没法看清楚,有另上图吧!


贴建表及插入记录的SQL,及要求结果出来看看
回复
LI8406 2011-12-30
哪个能看清罗?
回复
wwwwb 2011-12-30
贴建表及插入记录的SQL,及要求结果出来看看
回复
加载更多回复
相关推荐
发帖
数据库报表
创建于2007-09-28

6099

社区成员

其他数据库开发 数据库报表
申请成为版主
帖子事件
创建了帖子
2011-12-22 02:51
社区公告
暂无公告