php - 合并两个MySQL查询，分别工作正常

SELECT sum(monthly_target) as month_target
FROM `tbl_goal`
inner join user
    on tbl_goal.uid=user.id where user.store=1 and month='February'

结果：month_target = 9000

SELECT
    sum(net) as achieved,
    sum(hairs_total) as hairs_total,
    sum(beard_total) as beard_total,
    sum(product_total) as product_total
FROM `data`
inner join user
    on data.uid=user.id where user.store=1 and month='February'

结果：实现= 103毛总数= 63胡子总数= 40产品总数= 0

请给我任何提示，如何将它们融合为一体？

最佳答案

整rick。当前的联接条件意味着您要按用户进行聚合，但是WHERE子句清楚表明您需要存储级聚合。因此，我们可以尝试重写您的查询以按商店汇总。下面的两个子查询中的每一个执行单独的聚合，通过连接到用户表引入store id。然后，在外部，我们将user表连接到这些子查询中的每一个。

SELECT
    u.store,
    COALESCE(t1.achieved, 0) AS achieved,
    COALESCE(t1.hairs_total, 0) AS hairs_total,
    COALESCE(t1.beard_total, 0) AS beard_total,
    COALESCE(t1.product_total, 0) AS product_total,
    COALESCE(t2.month_target 0) AS month_target
FROM user u
LEFT JOIN
(
    SELECT
        usr.store,
        SUM(d.net) AS achieved,
        SUM(d.hairs_total) AS hairs_total,
        SUM(d.beard_total) AS beard_total,
        SUM(d.product_total) AS product_total
    FROM data d
    INNER JOIN user usr
        ON d.uid = usr.id
    WHERE d.month = 'February'
    GROUP BY usr.store
) t1
    ON u.store = t1.store
LEFT JOIN
(
    SELECT
        usr.store,
        SUM(t.monthly_target) AS month_target
    FROM tbl_goal t
    INNER JOIN user usr
        ON t.uid = usr.id
    WHERE t.month = 'February'
    GROUP BY usr.store
) t2
    ON u.store = t2.store;
WHERE
    u.store = 1;

如果要报告所有商店，只需删除外部WHERE子句。