如何添加为复杂MySQL查询返回的另一列

Question

我有一个非常复杂的MySQL查询，如下所示：

SELECT CONCAT(pe.first, ' ', pe.last) AS Name,
                   tp.EmpID as 'Empl ID',
                   DATE_FORMAT(tp.PunchDateTime, '%m-%d-%Y') AS 'Punch Date',
                   DATE_FORMAT(tp.PunchDateTime, '%W') AS 'Weekday',
                   TRUNCATE((SUM(UNIX_TIMESTAMP(PunchDateTime) * (1 - 2 * `In-Out`)) / 3600),2)
                      AS 'Hours Worked'
              FROM timeclock_punchlog tp LEFT JOIN prempl01 pe ON tp.EmpID = pe.prempl
             WHERE tp.PunchDateTime >= '2013-06-16' and tp.PunchDateTime < '2013-06-23'
             AND tp.EmpID = 1588
            GROUP BY date(PunchDateTime), EmpID
            ORDER BY Name, `Punch Date` ASC

现在我需要增加第6栏。我需要知道员工的午餐时间。我认为它将涉及select部分中的子查询，因为它太复杂了，无法以其他方式获得它。计算“工作时间”是很复杂的，因为我需要计算每天的(超时-时钟)+(超时-中断)。现在我也需要计算那些日子的破门而入。以下是一个雇员一天的当前表的结构。

PunchID EmpID   PunchEvent    PunchDateTime           In-Out
308     1588    clockin       6/17/2013 6:20:48 AM    Checked
313     1588    breakout      6/17/2013 12:15:18 PM   Unchecked
315     1588    breakin       6/17/2013 12:43:58 PM   Checked
319     1588    clockout      6/17/2013 5:00:37 PM    Unchecked

我不知道如何将午休时间添加到上面的查询中。希望我已经提供了所需的一切信息。

更新：--我已经为特定的一天和特定的员工设置了一个工作查询，可以完成我想要的任务。现在，我需要的是让这个查询在一个特定的日期范围(即一周)为所有员工工作。以下是查询：

SELECT CONCAT(pe.first, ' ', pe.last) AS Name,
   tp.EmpID AS 'Empl ID',
   DATE_FORMAT(tp.PunchDateTime, '%m-%d-%Y') AS 'Punch Date',
   DATE_FORMAT(tp.PunchDateTime, '%W') AS 'Weekday',
   TRUNCATE((SUM(UNIX_TIMESTAMP(PunchDateTime) * (1 - 2 * `In-Out`)) / 3600), 2)
      AS 'Hours Worked',
   (SELECT TIMEDIFF
 ((SELECT DATE_FORMAT(tpl.PunchDateTime, '%r') as dTime FROM timeclock_punchlog tpl WHERE tpl.PunchEvent = 'breakin' AND tpl.EmpID = 1588 AND DATE(tpl.PunchDateTime) = '2013-06-17'),
(SELECT DATE_FORMAT(tpl.PunchDateTime, '%r') as dTime FROM timeclock_punchlog tpl WHERE tpl.PunchEvent = 'breakout' AND tpl.EmpID = 1588 AND DATE(tpl.PunchDateTime) = '2013-06-17'))) as 'Lunch'
                      FROM timeclock_punchlog tp LEFT JOIN prempl01 pe ON tp.EmpID = pe.prempl
                     WHERE DATE(tp.PunchDateTime) = '2013-06-17'
                     AND tp.EmpID = 1588
                    GROUP BY date(PunchDateTime), EmpID
                    ORDER BY Name, `Punch Date` ASC

其结果是：

Name            Empl ID Punch Date  Weekday Hours Worked    Lunch
BRUCE COLEMAN   1588    06-17-2013  Monday  10.18           00:28:40

现在，如果有人可以不指定员工ID，也不指定确切的日期，就可以使该查询工作，那么将其改为日期范围。

Answer 1

您应该将两个tpl.EmpID = 1588替换为tpl.EmpID = tp.EmpID，并删除AND tp.EmpID = 1588。

编辑：

SELECT CONCAT(pe.first, ' ', pe.last) AS Name,
       tp.EmpID AS 'Empl ID',
       DATE_FORMAT(tp.PunchDateTime, '%m-%d-%Y') AS 'Punch Date',
       DATE_FORMAT(tp.PunchDateTime, '%W') AS 'Weekday',
       TRUNCATE((SUM(UNIX_TIMESTAMP(PunchDateTime) * (1 - 2 * `In-Out`)) / 3600), 2) AS 'Hours Worked',
       (SELECT TIMEDIFF((SELECT DATE_FORMAT(tpl.PunchDateTime, '%r') as dTime FROM timeclock_punchlog tpl WHERE tpl.PunchEvent = 'breakin' AND tpl.EmpID = tp.EmpID AND DATE(tpl.PunchDateTime) = '2013-06-17'),
                        (SELECT DATE_FORMAT(tpl.PunchDateTime, '%r') as dTime FROM timeclock_punchlog tpl WHERE tpl.PunchEvent = 'breakout' AND tpl.EmpID = tp.EmpID AND DATE(tpl.PunchDateTime) = '2013-06-17'))) as 'Lunch'
FROM timeclock_punchlog tp LEFT JOIN prempl01 pe ON tp.EmpID = pe.prempl
WHERE DATE(tp.PunchDateTime) = '2013-06-17'
GROUP BY date(PunchDateTime), EmpID
ORDER BY Name, `Punch Date` ASC