有这个7个表(其实就是7个工序),结构都基本上差不多,基本结构如下:
表一
id 操作工号
1 1
2 1
3 2
4 2
5 3
依次类推
我现在要从这7个表中,统计比如1号操作工出现的次数,2号,3号的类推
最后形成这样的查询结果:
工序(每个工序单独对应一个表) 操作工1 操作工2 操作工3
表一 2 2 1
表二
求写法
表一
id 操作工号
1 1
2 1
3 2
4 2
5 3
依次类推
我现在要从这7个表中,统计比如1号操作工出现的次数,2号,3号的类推
最后形成这样的查询结果:
工序(每个工序单独对应一个表) 操作工1 操作工2 操作工3
表一 2 2 1
表二
求写法
sum(case 操作工号 when 1 then 1 else 0 end) as 操作工1,
sum(case 操作工号 when 2 then 1 else 0 end) as 操作工2,
sum(case 操作工号 when 3 then 1 else 0 end) as 操作工3
from 表一union all
select '表二' as 工序,
sum(case 操作工号 when 1 then 1 else 0 end) as 操作工1,
sum(case 操作工号 when 2 then 1 else 0 end) as 操作工2,
sum(case 操作工号 when 3 then 1 else 0 end) as 操作工3
from 表二union all
...
'表一' as 工序,
sum(case 操作工号 when 1 then 1 else 0 end) as [操作工1],
sum(case 操作工号 when 2 then 1 else 0 end) as [操作工2],
sum(case 操作工号 when 3 then 1 else 0 end) as [操作工3]
from 表一
union all
select
'表二' as 工序,
sum(case 操作工号 when 1 then 1 else 0 end) as [操作工1],
sum(case 操作工号 when 2 then 1 else 0 end) as [操作工2],
sum(case 操作工号 when 3 then 1 else 0 end) as [操作工3]
from 表二
union all
select
'表三' as 工序,
sum(case 操作工号 when 1 then 1 else 0 end) as [操作工1],
sum(case 操作工号 when 2 then 1 else 0 end) as [操作工2],
sum(case 操作工号 when 3 then 1 else 0 end) as [操作工3]
from 表三
union all
select
'表四' as 工序,
sum(case 操作工号 when 1 then 1 else 0 end) as [操作工1],
sum(case 操作工号 when 2 then 1 else 0 end) as [操作工2],
sum(case 操作工号 when 3 then 1 else 0 end) as [操作工3]
from 表四
union all
select
'表五' as 工序,
sum(case 操作工号 when 1 then 1 else 0 end) as [操作工1],
sum(case 操作工号 when 2 then 1 else 0 end) as [操作工2],
sum(case 操作工号 when 3 then 1 else 0 end) as [操作工3]
from 表五
union all
select
'表六' as 工序,
sum(case 操作工号 when 1 then 1 else 0 end) as [操作工1],
sum(case 操作工号 when 2 then 1 else 0 end) as [操作工2],
sum(case 操作工号 when 3 then 1 else 0 end) as [操作工3]
from 表六
union all
select
'表七' as 工序,
sum(case 操作工号 when 1 then 1 else 0 end) as [操作工1],
sum(case 操作工号 when 2 then 1 else 0 end) as [操作工2],
sum(case 操作工号 when 3 then 1 else 0 end) as [操作工3]
from 表七