急急急！求个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

回复赞