Mysql加入/联合问题

Question

我正在尝试创建一个从 2 个表中提取数据的报告:a 和 b。该报告按 a.clock 分组。报告的大部分数据来自 - 那部分工作正常。 a.clock 与 b.userID 的链接。

我正在努力处理的部分是数据来自 b 的列之一。我需要为主报告中的每个 a.clock 分组汇总以下内容(此查询独立运行)

 SELECT (
   SUM(
     TIME_TO_SEC(
       TIMEDIFF(
         CONCAT(b.endDate, ' ', b.outTime),
         CONCAT(b.startDate, ' ', b.inTime)
       )
     ) / 3600
   )
 ) AS 'Misc Hours' FROM b

换句话说，我需要计算每个 a.clock 的杂项小时数(b 中)。我想也许加入 b 表是必要的，但这似乎没有用。有什么建议吗？

这是表格定义(抱歉，冗长)

CREATE TABLE `a` (
  `laborID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '0=sched; 1=accepted; 2=complete; 3=authorize',
  `laborType` varchar(15) NOT NULL DEFAULT '0' COMMENT 'Lookup',
  `hours` varchar(15) NOT NULL DEFAULT '',
  `wage` varchar(15) NOT NULL DEFAULT '',
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `laborDate` date NOT NULL,
  `ot` varchar(15) NOT NULL,
  `clock` varchar(15) NOT NULL,
  `setup` varchar(15) NOT NULL DEFAULT '',
  `ot2` varchar(15) NOT NULL,
  `wageOT1` varchar(15) NOT NULL,
  `wageOT2` varchar(15) NOT NULL,
  `inTime` varchar(15) NOT NULL,
  `outTime` varchar(15) NOT NULL,
  `startDateString` varchar(125) NOT NULL,
  `endDateString` varchar(125) NOT NULL,
  `authRequestDate` datetime NOT NULL,
  `authRequestUser` int(10) unsigned NOT NULL,
  `authRequestReason` text NOT NULL,
  `authDate` datetime NOT NULL,
  `authUser` int(10) unsigned NOT NULL,
  `authRequired` varchar(45) NOT NULL,
  `km` varchar(45) NOT NULL,
  `travelTime` varchar(45) NOT NULL,
  `actualInTime` varchar(45) NOT NULL,
  `actualOutTime` varchar(45) NOT NULL,
  `actualKm` varchar(45) NOT NULL,
  `actualTravelTime` varchar(45) NOT NULL,
  `intNotes` text,
  `extNotes` text,
  `billableReason` text,
  `billableHours` varchar(45) DEFAULT NULL,
  `actualHours` varchar(45) DEFAULT NULL,
  `overtime` varchar(45) DEFAULT NULL,
  `followUpReason` text NOT NULL,
  `responseType` varchar(45) DEFAULT NULL,
  `followUpType` int(10) unsigned DEFAULT NULL,
  `billableDrop` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`laborID`),
  KEY `id` (`id`),
  KEY `type` (`type`),
  KEY `laborDate` (`laborDate`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;


CREATE TABLE `b` (
  `timecodeID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userID` int(10) unsigned NOT NULL,
  `inTime` varchar(45) NOT NULL,
  `outTime` varchar(45) NOT NULL,
  `startDateString` varchar(145) NOT NULL,
  `endDateString` varchar(145) NOT NULL,
  `startDate` varchar(45) NOT NULL,
  `endDate` varchar(45) NOT NULL,
  `type` varchar(45) NOT NULL,
  `comments` text NOT NULL,
  `multiDay` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`timecodeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Answer 1

好吧，如果您在每个表中有多个行，那么您将获得更大的总和，因为 labor 中的每一行都将与时间码中的每一行连接。因此，我们的想法是根据时钟和用户 ID 对嵌套查询进行分组，以根据分组分别获取一行

SELECT A.Clock, A.hours, B.'Misc Hours'
  FROM
     (
        SELECT labor.clock,
               SUM(hours) Hours
          FROM labor
         GROUP BY labor.clock
     ) A
 INNER JOIN 
     (
        SELECT  timecodes.userID,
                (SUM(
                     TIME_TO_SEC(
                          TIMEDIFF(
                               CONCAT(timecodes.endDate,' ',timecodes.outTime),
                               CONCAT(timecodes.startDate,' ',timecodes.inTime)
                          )
                      )/3600
                  )
                 ) AS 'Misc Hours'
         FROM timecodes
        GROUP BY timecodes.userID

) AS B ON A.Clock = B.UserId