mysql查询从两组不同的表中分别选择两列

Question

需要从两组不同的表中分别选择两列。

现在，我把它作为两个不同的查询，但是想知道是否有一种方法可以在一个查询中实现这一点。

查询-1

 SELECT     
        BSM, DATE, HOUR, SUM(t.ATT), SUM(t.AVG_HLD)
    FROM (
        SELECT BSM, DATE, HOUR, ATT, AVG_HLD FROM t1 WHERE  ".$whereCondition." UNION ALL
        SELECT BSM, DATE, HOUR, ATT, AVG_HLD FROM t2 WHERE  ".$whereCondition." 
        ) t
    GROUP BY BSM, DATE, HOUR ";

查询-2

 SELECT     
        BSM, DATE, HOUR, SUM(s.M_ATT), SUM(s.M_AVG_HLD)
    FROM (
        SELECT BSM, DATE, HOUR, M_ATT, AVG_HLD FROM t3 WHERE    ".$whereCondition." UNION ALL
        SELECT BSM, DATE, HOUR, M_ATT, AVG_HLD FROM t4 WHERE    ".$whereCondition."
        ) s
    GROUP BY BSM, DATE, HOUR ";

如何将查询-1和查询-2结合起来

 SELECT     
        BSM, DATE, HOUR, SUM(s.ATT), SUM(s.AVG_HLD), SUM(s.M_ATT), SUM(s.M_AVG_HLD)
    FROM (
        ** Whatever it is **
        ) 
    GROUP BY BSM, DATE, HOUR ";

Answer 1

对所有4个子查询创建一个UNION，为来自另一对表的列插入0列。

SELECT BSM, DATE, HOUR, SUM(ATT) AS ATT, SUM(AVG_HLD) AS AVG_HLD, SUM(M_ATT) AS M_ATT, SUM(M_AVG_HLD) AS M_AVG_HLD
FROM (
        SELECT BSM, DATE, HOUR, ATT, AVG_HLD, 0 AS M_ATT, 0 AS M_AVG_HLD FROM t1 WHERE  ".$whereCondition." 
        UNION ALL
        SELECT BSM, DATE, HOUR, ATT, AVG_HLD, 0 AS M_ATT, 0 AS M_AVG_HLD FROM t2 WHERE  ".$whereCondition." 
        UNION ALL
        SELECT BSM, DATE, HOUR, 0 AS ATT, 0 AS AVG_HLD, M_ATT, M_AVG_HLD FROM t3 WHERE    ".$whereCondition." 
        UNION ALL
        SELECT BSM, DATE, HOUR, 0 AS ATT, 0 AS AVG_HLD, M_ATT, M_AVG_HLD FROM t4 WHERE    ".$whereCondition."
    ) u
GROUP BY BSM, DATE, HOUR

Answer 2

你也可以这样做。

SELECT bsm, date, hour, 
       SUM(CASE WHEN src = 1 THEN att END) att, 
       SUM(CASE WHEN src = 1 THEN att_hld END) att_hld, 
       SUM(CASE WHEN src = 2 THEN att END) m_att, 
       SUM(CASE WHEN src = 2 THEN att_hld END) m_att_hld
FROM 
(
    SELECT 1 src, bsm, date, hour, att, avg_hld FROM t1 WHERE  ".$whereCondition." UNION ALL
    SELECT 1 src, bsm, date, hour, att, avg_hld FROM t2 WHERE  ".$whereCondition." UNION ALL
    SELECT 2 src, bsm, date, hour, m_att, avg_hld FROM t3 WHERE ".$whereCondition." UNION ALL
    SELECT 2 src, bsm, date, hour, m_att, avg_hld FROM t4 WHERE ".$whereCondition."
) t
 GROUP BY bsm, date, hour;