数据表如下:pj_id hours type (此处type=1代表“正常上班”type=2代表“加班”)
1 10 1
1 20 2
1 15 1
2 11 1
2 12 2
2 17 2现在想要得到一个结果为:(统计同一个项目的正常工时总数和加班工时总数) pj_id hours1 hours2
1 25(为10+20所得) 20
2 11 29(为12+19所得)
1 10 1
1 20 2
1 15 1
2 11 1
2 12 2
2 17 2现在想要得到一个结果为:(统计同一个项目的正常工时总数和加班工时总数) pj_id hours1 hours2
1 25(为10+20所得) 20
2 11 29(为12+19所得)
sum(case when type=1 then hours else 0 end) as hours1,
sum(case when type=2 then hours else 0 end) as hours2
from 数据表
group by pj_id[align=center]==== 思想重于技巧 ====
[/align]
sum(hours * (type div 2)) as hours1,
sum(hours * (type-1)) as hours2
from 数据表
group by pj_id
[align=center]==== 思想重于技巧 ====
[/align]