mysql - 将 union 和 sum 与不同的 View 列结合起来

Question

我在这里查看

我想尝试的是让 v_total_project 看起来像这样。我已经尝试过这段代码

select o.id_project AS id_project,
sum((o.office_expense + m.misc_expense)) AS total_expense 
from v_office_project o inner join v_misc_project m on o.id_project = m.id_project
group by o.id_project, m.id_project

我知道我的代码不会打印v_misc_project.id_project。因此，我尝试使用 Union 来打印 id_project，但出现错误:不同的列数。

select o.id_project AS id_project
from v_office_project o
union
select m.id_project as id_project,
sum((o.office_expense + m.misc_expense)) AS total_expense 
from v_office_project o inner join v_misc_project m on o.id_project = m.id_project
group by id_project

那么，还有其他方法吗？提前致谢，

Answer 1

SELECT
    o.id_project AS id_project,
    sum(
        o.office_expense + ifnull(m.misc_expense, 0)
    ) AS total_expense
FROM
    v_office_project o
LEFT JOIN v_misc_project m ON o.id_project = m.id_project
GROUP BY
    id_project
UNION
    SELECT
        m.id_project AS id_project,
        sum(
            ifnull(o.office_expense, 0) + m.misc_expense
        ) AS total_expense
    FROM
        v_office_project o
    RIGHT JOIN v_misc_project m ON o.id_project = m.id_project
    GROUP BY
        id_project

在您联盟的第一个选择中，您缺少费用字段。

连接也丢失了，已添加。使用 LEFT 或 RIGHT Join 代替 INNER JOIN，否则您只能得到 id_project 的结果，即在两个表中。

在计算中添加了ifnull()，因为LEFT/RIGHT连接可以返回null，这必须被视为 0。