时间戳

首页 > 解决方案 > 如何在 mysql 中使用结果变量执行计算，尝试 sum () 但在这里不起作用

php - 如何在 mysql 中使用结果变量执行计算，尝试 sum () 但在这里不起作用

问题描述

我不知道如何进行计算，我知道如何在 php 中计算结果。但我确信在查询中必须有办法做到这一点。

我需要结果中的 projectTotal 和 opdrachtTotal

SELECT
TABLE_PROJECTEN.id,
TABLE_PROJECTEN.code as pCode,
TABLE_OPDRACHTEN.code as oCode,
(SELECT COALESCE(SUM(t_kosten.aantal * t_kosten.bedrag),0) FROM `t_kosten` WHERE t_kosten.id_ref_project = TABLE_PROJECTEN.id) AS projectKosten,
(SELECT COALESCE(SUM(t_opbrengsten.aantal * t_opbrengsten.bedrag),0) FROM `t_opbrengsten` WHERE t_opbrengsten.id_ref_project = TABLE_PROJECTEN.id) AS projectOpbrengsten,
(SELECT COALESCE(SUM(t_kosten.aantal * t_kosten.bedrag),0)  FROM `t_kosten` WHERE t_kosten.id_ref_opdracht =TABLE_OPDRACHTEN.id) AS opdrachtKosten
(SELECT COALESCE(SUM(t_opbrengsten.aantal * t_opbrengsten.bedrag),0)  FROM `t_opbrengsten` WHERE t_opbrengsten.id_ref_opdracht =TABLE_OPDRACHTEN.id) AS opdrachtOpbrengsten,
(CALCULATE HERE: projectOpbrengsten-projectKosten) as projectTotal,
(CALCULATE HERE: opdrachtOpbrengsten-opdrachtKosten) as opdrachtTotal
FROM
t_project TABLE_PROJECTEN
LEFT JOIN t_opdracht TABLE_OPDRACHTEN on TABLE_OPDRACHTEN.id_ref_project =  TABLE_PROJECTEN.id
GROUP BY TABLE_OPDRACHTEN.code

标签： phpmysql

解决方案

您可以在单个子子句中执行计算，并与主查询而不是依赖子查询连接。这样做，您可以重用子子句的结果以获取总数。

SELECT
    p.id,
    p.code AS pCode,
    o.code AS oCode,
    COALESCE(tk1.projectKosten,0) AS projectKosten,
    COALESCE(to1.projectOpbrengsten,0) AS projectOpbrengsten,
    COALESCE(tk2.opdrachtKosten,0) AS opdrachtKosten
    COALESCE(to2.opdrachtOpbrengsten,0) AS opdrachtOpbrengsten,
    (COALESCE(to1.projectOpbrengsten,0) - COALESCE(tk1.projectKosten,0)) AS projectTotal,
    (COALESCE(to2.opdrachtOpbrengsten,0) - COALESCE(tk2.opdrachtKosten,0) ) AS opdrachtTotal
FROM t_project p
LEFT JOIN t_opdracht o ON o.id_ref_project =  p.id projectKosten
LEFT JOIN (
    SELECT id_ref_project COALESCE(SUM(aantal * bedrag),0) 
    FROM `t_kosten` 
    GROUP id_ref_project
) tk1 ON  tk1.id_ref_project = p.id
LEFT JOIN (
    SELECT id_ref_project, SUM(aantal * bedrag) projectOpbrengsten 
    FROM `t_opbrengsten` 
    GROUP BY id_ref_project
) to1 ON to1.id_ref_project = p.id
LEFT JOIN (
    SELECT id_ref_opdracht ,SUM(t_kosten.aantal * t_kosten.bedrag) opdrachtKosten  
    FROM `t_kosten` 
    GROUP BY id_ref_opdracht
) tk2 ON tk2.id_ref_opdracht =o.id
LEFT JOIN (
    SELECT id_ref_opdracht,SUM(aantal * bedrag) AS opdrachtOpbrengsten
    FROM `t_opbrengsten` 
    GROUP BY id_ref_opdracht
) to2 ON to2.id_ref_opdracht =o.id

我也看不出做 group by on 的目的TABLE_OPDRACHTEN.code

推荐阅读