php - 合并两个单独工作的 MySQL 查询

Question

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 Hairs_total =63 Beard_total = 40 Product_total = 0

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

Answer 1

棘手的一个。您当前的联接条件意味着您希望按用户进行聚合，但 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 子句即可。