将两个mySQL sum查询合并为一个使用不同where子句的查询

opr 发表于 Dev

OPR

我有两个求和引用MySQL中的不同表。如何将两者结合起来，以便为数据库中的每个用户计算两个值之间的差异？

查询1：

SELECT tbl_users.name, tbl_holiday_allocation.`year`,  
tbl_holiday_allocation.allocation, 
tbl_holiday_allocation.carried_forward, 
tbl_holiday_allocation.lieu_days,  
SUM(tbl_holiday_allocation.allocation+tbl_holiday_allocation.carried_forward+tbl_holiday_allocation.lieu_days) AS TotalAllocation  
FROM tbl_holiday_allocation  
RIGHT JOIN tbl_users ON tbl_users.username = tbl_holiday_allocation.username  
WHERE year = 2014 AND user_active = 1  
AND (jobtitle like '".$search_string3."' OR department like '".$search_string3."') 
GROUP BY tbl_users.name 
ORDER BY tbl_users.id ASC

查询2

SELECT tbl_users.name, Sum(tbl_holidays.NumberOfDays) AS ApprovedTotal  
FROM tbl_users  
LEFT JOIN tbl_holidays ON tbl_users.username = tbl_holidays.username  
WHERE ((NumberOfDays < 6 AND LMStatus = 1 AND (department = 'Qual' or department = 'Quant' or department = 'Qual/Quant') AND RequestType = 'Holiday')  
OR (NumberOfDays < 10 AND LMStatus = 1 AND (department = 'Accounts' or department = 'HR' or department = 'IT'  or department = 'Support' or department = 'Operations') AND RequestType = 'Holiday')  
OR (NumberOfDays > 5 AND NumberOfDays < 10 AND LMStatus = 1 AND HOStatus = 1 AND (department = 'Qual' or department = 'Quant' or department = 'Qual/Quant') AND RequestType = 'Holiday')  
OR (NumberOfDays > 9 AND 'LMStatus' = 1 AND HOStatus = 1 AND CEOStatus = 1 AND (department = 'Qual' or department = 'Quant' or department = 'Qual/Quant') AND RequestType = 'Holiday')  
OR (NumberOfDays > 9 AND LMStatus = 1 AND HOStatus = 1 AND CEOStatus = 1 AND (department = 'Qual' or department = 'Quant' or department = 'Qual/Quant') AND RequestType = 'Holiday')  OR (NumberOfDays > 9 AND LMStatus = 1 AND CEOStatus = 1 AND (department = 'Accounts' or department = 'Support' or department = 'HR' or department = 'IT' or department = 'Operations') AND RequestType = 'Holiday'))  
AND user_active = 1   
AND (jobtitle like '".$search_string3."' OR department like '".$search_string3."')  
GROUP BY tbl_users.name  
ORDER BY tbl_users.id ASC

最后，我希望能够采用SUM TotalAllocation-SUM ApprovedTotal

用户表：

CREATE TABLE `tbl_users` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `1` (`username`)
) ENGINE=MyISAM AUTO_INCREMENT=144 DEFAULT CHARSET=utf8

假期分配表：

CREATE TABLE `tbl_holiday_allocation` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `year` varchar(4) DEFAULT NULL,
  `allocation` int(3) DEFAULT NULL,
  `carried_forward` int(3) DEFAULT '0',
  `lieu_days` decimal(3,1) DEFAULT '0.0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=93 DEFAULT CHARSET=latin1

假期表

CREATE TABLE `tbl_holidays` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `StartDate` varchar(255) DEFAULT NULL,
  `EndDate` varchar(255) DEFAULT NULL,
  `RequestType` varchar(255) DEFAULT NULL,
  `WhichHalf` varchar(3) DEFAULT '0',
  `Details` varchar(255) DEFAULT NULL,
  `UserStatus` int(1) DEFAULT '0',
  `LMStatus` int(1) DEFAULT '0',
  `HOStatus` int(1) DEFAULT '0',
  `CEOStatus` int(1) DEFAULT '0',
  `HRStatus` int(1) DEFAULT '0',
  `NumberOfDays` decimal(3,1) DEFAULT NULL,
  `user_comments` varchar(255) DEFAULT NULL,
  `LMDeclineComment` varchar(255) DEFAULT NULL,
  `HODeclineComment` varchar(255) DEFAULT NULL,
  `CEODeclineComment` varchar(255) DEFAULT NULL,
  `HRDeclineComment` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=579 DEFAULT CHARSET=utf8

象征豆

SELECT q1..name, q1.`year`,  
  q1.allocation, q1.carried_forward, 
  q1.lieu_days, q1.TotalAllocation,
  q2.ApprovedTotal 
FROM (
   ~~INSERT Query1 here ~~
) AS q1
INNER JOIN
(
   ~~INSERT Query2 here ~~
) as q2
ON q1.username=q2.username;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。