表结构:
表1
胎号,接班编号
表2接班主表
接班编号,班次,班组
表3接班明细表
接班编号,岗位编号,人员编号
注:接班编号的前8为yymmdd的日期
先需要查询出
日期,班次,班组,班长(岗位编号为0038),操作主手(编号为0061),其他人员(其他的人员全部列上),数量。
表1
胎号,接班编号
表2接班主表
接班编号,班次,班组
表3接班明细表
接班编号,岗位编号,人员编号
注:接班编号的前8为yymmdd的日期
先需要查询出
日期,班次,班组,班长(岗位编号为0038),操作主手(编号为0061),其他人员(其他的人员全部列上),数量。
Select left(a.接班编号,8) as 日期,a.班次,
班长=count(case when b.岗位编号='0038' then b.人员编号 else NULL end),
操作主手=count(case when b.岗位编号='0061' then b.人员编号 else NULL end),
其他人员=count(case when b.岗位编号 not in ('0038','0061') then b.人员编号 else NULL end)
数量=count(1)
from 表2 a inner join 表3 b on a.接班编号=b.接班编号
group by left(a.接班编号,8),a.班次
SELECT '20'+LEFT(t2.xr_ShiftId,6) AS '日期',
t3.sm_DayShiftID as '班次',
t3.sm_GroupID as '班组',
(select PNAME from HPINF t5,dbo.tb_qu_ShiftDetail t4 where t5.PERID=t4.sd_personid and t4.sd_positionid='0038' and t4.sd_ShiftId=t2.xr_ShiftId)as '班长',
(select top 1 PNAME from HPINF t6,dbo.tb_qu_ShiftDetail t7 where t6.PERID=t7.sd_personid and t7.sd_positionid='0061' and t7.sd_ShiftId=t2.xr_ShiftId)as '操作主手'
,dbo.F_GetPersonInfo(t2.xr_ShiftId) as '其他人员',
(select count(1) from Tb_qu_XcheckRecord t1 where t1.xr_ShiftId=t2.xr_ShiftId) as '数量'
FROM dbo.Tb_qu_XcheckRecord t2,tb_qu_ShiftMaster t3
where t2.xr_ShiftId=t3.sm_ShiftId
group by t2.xr_ShiftId,t3.sm_DayShiftId,t3.sm_GroupID
order by t2.xr_ShiftId,t3.sm_DayShiftId,t3.sm_GroupID
其中Tb_qu_XcheckRecord 为表1,tb_qu_ShiftMaster 为接班主表,tb_qu_ShiftDetail为接班明细表,班次和班组还没有转换过去,HPINF为人员信息表。
F_GetPersonInfo为自定义的函数。
上述句子可以得到结果,但是太麻烦,寻求更简捷的方法来优化,提高性能,最好不用子查询。
A.日期, A.班次, A.班组,
B.班长, B.操作主手,
dbo.F_GetPersonInfo(t2.xr_ShiftId) as '其他人员',
C.数量
FROM(
SELECT t2.xr_ShiftId, t3.sm_DayShiftId, t3.sm_GroupID,
'20'+LEFT(t2.xr_ShiftId,6) AS '日期',
t3.sm_DayShiftID as '班次',
t3.sm_GroupID as '班组'
FROM dbo.Tb_qu_XcheckRecord t2, tb_qu_ShiftMaster t3
where t2.xr_ShiftId=t3.sm_ShiftId
group by t2.xr_ShiftId,t3.sm_DayShiftId,t3.sm_GroupID
)A
INNER JOIN(
select t4.xr_ShiftId,
班长 = MAX(CASE WHEN t4.sd_positionid='0038' THEN PNAME ELSE '' END),
操作主手 = MAX(CASE WHEN t4.sd_positionid='0061' THEN PNAME ELSE '' END)
from HPINF t5, dbo.tb_qu_ShiftDetail t4
where t5.PERID=t4.sd_personid
and t4.sd_positionid IN('0038', '0061')
group by t4.xr_ShiftId
)B
ON A.xr_ShiftId = B.xr_ShiftId
INNER JOIN(
select xr_ShiftId,
数量 = count(1)
from Tb_qu_XcheckRecord t1
GROUP BY t1.xr_ShiftId
)C
ON A.xr_ShiftId = B.xr_ShiftId
order by A.xr_ShiftId, A.sm_DayShiftId, A.sm_GroupID
SELECT A.日期, A.班次, A.班组,
B.班长, B.操作主手,
dbo.F_GetPersonInfo(A.xr_ShiftId) as '其他人员',
C.数量
FROM(SELECT t2.xr_ShiftId,t4.COCDE,t5.WRKNM,
'20'+LEFT(t2.xr_ShiftId,6) AS '日期',
t4.COCDE as '班次',
t5.WRKNM as '班组'
FROM dbo.Tb_qu_XcheckRecord t2, tb_qu_ShiftMaster t3,HTCOC t4,HPWPD t5
where t2.xr_ShiftId=t3.sm_ShiftId and t3.sm_DayShiftID=right(t4.COCID,2) and t3.sm_GroupID=t5.WPDID
group by t2.xr_ShiftId,t4.COCDE,t5.WRKNM
)A
INNER JOIN(
select t4.sd_ShiftId,
班长 = MAX(CASE WHEN t4.sd_positionid='0038' THEN PNAME ELSE '' END),
操作主手 = MAX(CASE WHEN t4.sd_positionid='0061' THEN PNAME ELSE '' END)
from HPINF t5, dbo.tb_qu_ShiftDetail t4
where t5.PERID=t4.sd_personid
and t4.sd_positionid IN('0038', '0061')
group by t4.sd_ShiftId
)B
ON A.xr_ShiftId = B.sd_ShiftId
INNER JOIN(
select xr_ShiftId,
数量 = count(1)
from Tb_qu_XcheckRecord t1
GROUP BY t1.xr_ShiftId
)C
ON C.xr_ShiftId = B.sd_ShiftId
where A.日期 >='20060101' and A.日期<='20070101' and A.班次 = '早班' and A.班组 = '甲'
order by A.xr_ShiftId,A.COCDE,A.WRKNM