mysql 跨表和分组的列求和

Question

需要对多个表中的不同列进行求和，然后按不同的列进行分组。

作为此过程的一部分，尝试以下方法对不同表中的单个列进行求和。

SELECT SUM(t.Qty) AS total_qty
    FROM (SELECT Qty FROM MC
          UNION ALL
          SELECT Qty FROM Amex) t

但是我需要找到以下内容

(总和(t1.ATT) + 总和(t2.ATT) + 总和(t3.ATT) + 总和(t4.ATT)) -

(sum(t1.RE_ORG) + sum(t2.RE_ORG) + sum(t3.RE_ORG) + sum(t4.RE_ORG) ) AND 按 BSM 或日期、时间分组

不确定如何进一步进行。

任何帮助将不胜感激!!!

不同的表:t1、t2、t3、t4

mysql> show create table t1\G
*************************** 1. row *************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `BSM` varchar(32) NOT NULL,
  `Date` date DEFAULT NULL,
  `Hour` time DEFAULT NULL,
  `BSC` float DEFAULT NULL,
  `BTS` float DEFAULT NULL,
  `SECTOR` float DEFAULT NULL,
  `BAND` float DEFAULT NULL,
  `FA` float DEFAULT NULL,
  `ASSGN_BAND` float DEFAULT NULL,
  `ATT` float DEFAULT NULL,
  `RE_ORG` float DEFAULT NULL,
  `OVD_CMP` float DEFAULT NULL,
  `OVD_BCP` float DEFAULT NULL,
  `A1_OOS` float DEFAULT NULL,
  `CMP_MSC` float DEFAULT NULL,
  `BS_REL1` float DEFAULT NULL,
  `MSC_REL1` float DEFAULT NULL,
  `MOB_REL1` float DEFAULT NULL,
  `ASR` float DEFAULT NULL,
  `ATP_UNA` float DEFAULT NULL,
  `CMP_ATP` float DEFAULT NULL,
  `CE_UNA` float DEFAULT NULL,
  `WALSH_UNA` float DEFAULT NULL,
  `TCH_OVP` float DEFAULT NULL,
  `SYN_TO` float DEFAULT NULL,
  `BCP_CEP` float DEFAULT NULL,
  `CMP_BCP` float DEFAULT NULL,
  `NOT_ACQ` float DEFAULT NULL,
  `MS_ATP1` float DEFAULT NULL,
  `MS_ATP2` float DEFAULT NULL,
  `SO_REJ` float DEFAULT NULL,
  `REV_NO_FRM_FROM_CEP2` float DEFAULT NULL,
  `FWD_NO_FRM_FROM_ATP2` float DEFAULT NULL,
  `BAD_FRM2` float DEFAULT NULL,
  `BS_REL2` float DEFAULT NULL,
  `MSC_REL2` float DEFAULT NULL,
  `MOB_REL2` float DEFAULT NULL,
  `ASC1` float DEFAULT NULL,
  `ASC2` float DEFAULT NULL,
  `REV_NO_FRM_FROM_CEP3` float DEFAULT NULL,
  `FWD_NO_FRM_FROM_ATP3` float DEFAULT NULL,
  `FWD_NO_FRM_FROM_MGW3` float DEFAULT NULL,
  `BAD_FRM3` float DEFAULT NULL,
  `BS_REL3` float DEFAULT NULL,
  `MSC_REL3` float DEFAULT NULL,
  `MOB_REL3` float DEFAULT NULL,
  `ANS` float DEFAULT NULL,
  `REV_NO_FRM_FROM_CEP4` float DEFAULT NULL,
  `FWD_NO_FRM_FROM_ATP4` float DEFAULT NULL,
  `FWD_NO_FRM_FROM_MGW4` float DEFAULT NULL,
  `BAD_FRM4` float DEFAULT NULL,
  `BS_REL4` float DEFAULT NULL,
  `MSC_REL4` float DEFAULT NULL,
  `MOB_REL4` float DEFAULT NULL,
  `AVG_ANS` float DEFAULT NULL,
  `AVG_CPL` float DEFAULT NULL,
  `AVG_DRP` float DEFAULT NULL,
  `AVG_TRF` float DEFAULT NULL,
  `AVG_HLD` float DEFAULT NULL,
  `AVG_DLY` float DEFAULT NULL,
  `LINK_UNA` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `BSM` (`BSM`),
  KEY `ATT` (`ATT`),
  KEY `Date` (`Date`),
  KEY `Hour` (`Hour`)
) ENGINE=InnoDB AUTO_INCREMENT=349625 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

Answer 1

您正在寻找这样的东西吗？

SELECT BSM, SUM(t.ATT) - SUM(t.RE_ORG)
FROM (SELECT BSM, `DATE`, `HOUR`, ATT, RE_ORG FROM t1 union all
      SELECT BSM, `DATE`, `HOUR`, ATT, RE_ORG FROM t2 union all
      SELECT BSM, `DATE`, `HOUR`, ATT, RE_ORG FROM t3 union all
      SELECT BSM, `DATE`, `HOUR`, ATT, RE_ORG FROM t4
     ) t
GROUP BY BSM;