问题 工作量统计:表1:employeeid name
123 lee
122 zhang
121 wang
124 zhao表2:workwork_id work1 work2 work3 work4
100 123 122 121
101 124 122 121
102 122 123 124 121
103 121 122 123 124
表3:work_simple
work_id work1_s work2_s
1001 122 123
1002 123 124
1003 123 121 输出所有人的工作量:
name work1 work2 work3 work4 work1_s work2_s
姓名 工作量1 工作量2 工作量3 工作量4 工作量5 工作量6 注:工作量——参与某一项工作的次数,如122参与了work2两次,即“工作量2=2”数据库为Oracle9i,有没有比较好的解决方案,因为实际要统计的项目(即到工作量14)有14个,如果一直用union all也可以完成,但是Select语句太庞大了,而且效率也是个问题,
123 lee
122 zhang
121 wang
124 zhao表2:workwork_id work1 work2 work3 work4
100 123 122 121
101 124 122 121
102 122 123 124 121
103 121 122 123 124
表3:work_simple
work_id work1_s work2_s
1001 122 123
1002 123 124
1003 123 121 输出所有人的工作量:
name work1 work2 work3 work4 work1_s work2_s
姓名 工作量1 工作量2 工作量3 工作量4 工作量5 工作量6 注:工作量——参与某一项工作的次数,如122参与了work2两次,即“工作量2=2”数据库为Oracle9i,有没有比较好的解决方案,因为实际要统计的项目(即到工作量14)有14个,如果一直用union all也可以完成,但是Select语句太庞大了,而且效率也是个问题,
work1_s指什么,还有表2和表3中work_id是一样的吗,表示工作类型?
name work1 work2 work3 work4 work1_s work2_s
lee 1 1 1 0 2 1
select id, sum(work1) as 工作量1, sum(work2) as 工作量2, sum(work3) as 工作量3
from (
select work1 as id, count(*) as work1, 0 as work2, 0 as work3
from work
group by work1
union all
select work2 as id, 0 as work1, count(*) as work2, 0 as work3
from work
group by work2
union all
select work3 as id, 0 as work1, 0 as work2, count(*) as work3
from work
group by work3
)
where id is not null and id <> 'null'
group by id;求比较好的解决方案!!!