[讨论]用查询统计员工所在工位作业时间

zyz218

谢谢WZJ与ANDYMARK的大力支持，在下受益非浅！

andymark

用左联接重写了查询
SELECT c.系统编号, c.工号, c.工位能力, c.作业日期, d.结束日期, IIf(IsNull(d.结束日期),datediff("d",c.作业日期,format(now(),"yyyy-mm-dd")),datediff("d",c.作业日期,d.结束日期)) AS 天数
FROM 表1 AS c LEFT JOIN [SELECT a.系统编号, a.工号, a.工位能力, a.作业日期, min(b.作业日期) AS 结束日期
FROM 表1 AS a INNER JOIN 表1 AS b ON a.工号=b.工号
WHERE b.作业日期>a.作业日期
GROUP BY a.工号, a.系统编号, a.工位能力, a.作业日期
]. AS d ON val(c.系统编号)=val(d.系统编号)
ORDER BY c.工号, c.系统编号;

andymark

整合到交叉表
TRANSFORM Sum(e.天数) AS 天数之总计
SELECT e.工号, Sum(e.天数) AS [总计 天数]
FROM [SELECT c.系统编号, c.工号, c.工位能力, c.作业日期, d.结束日期, IIf(IsNull(d.结束日期),datediff("d",c.作业日期,format(now(),"yyyy-mm-dd")),datediff("d",c.作业日期,d.结束日期)) AS 天数
FROM 表1 AS c LEFT JOIN [SELECT a.系统编号, a.工号, a.工位能力, a.作业日期, min(b.作业日期) AS 结束日期
FROM 表1 AS a INNER JOIN 表1 AS b ON a.工号=b.工号
WHERE b.作业日期>a.作业日期
GROUP BY a.工号, a.系统编号, a.工位能力, a.作业日期
]. AS d ON val(c.系统编号)=val(d.系统编号)
ORDER BY c.工号, c.系统编号]. AS e
GROUP BY e.工号
PIVOT e.工位能力;