将并和与不同的观点列结合起来

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。因此，我尝试联合打印两个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

在你的工会的第一次选择中，你失去了支付开支的机会。

另外，连接也丢失了，添加了它。而不是INNER JOIN，而是使用LEFT或RIGHT Join，否则您只能得到两个表中的id_project的结果。

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