如何不使用UNION合并两个SQL查询并正确统计收支日汇总

你的问题出在直接关联sales和purchase表时产生了笛卡尔积：比如某天销售有2条记录、采购有3条记录，关联后会生成6条配对记录，SUM计算时销售金额会被重复累加3次，采购金额被重复累加2次，自然统计值就错了。

正确的做法是先分别统计两张表的日汇总数据，再通过日期关联，确保每个日期的销售和采购金额都是独立计算的。

方案一（支持全外连接的数据库，如PostgreSQL、SQL Server） #

SELECT 
    COALESCE(s.dates, p.dates) AS date,
    COALESCE(s.sales_total, 0) AS sales_total,
    COALESCE(p.purchase_total, 0) AS purchase_total
FROM 
    (SELECT date AS dates, SUM(price) AS sales_total FROM sales GROUP BY date) s
FULL OUTER JOIN 
    (SELECT date AS dates, SUM(price) AS purchase_total FROM purchase GROUP BY date) p
ON 
    s.dates = p.dates
ORDER BY 
    date;

方案二（MySQL等不支持全外连接的数据库） #

先用UNION获取所有存在交易的日期，再分别左连接两个汇总表：

SELECT 
    all_dates.date,
    COALESCE(s.sales_total, 0) AS sales_total,
    COALESCE(p.purchase_total, 0) AS purchase_total
FROM 
    (SELECT date FROM sales UNION SELECT date FROM purchase) all_dates
LEFT JOIN 
    (SELECT date, SUM(price) AS sales_total FROM sales GROUP BY date) s 
    ON all_dates.date = s.date
LEFT JOIN 
    (SELECT date, SUM(price) AS purchase_total FROM purchase GROUP BY date) p 
    ON all_dates.date = p.date
ORDER BY 
    date;

对应的PHP代码修改 #

把你的查询替换成上面的SQL，然后调整循环逻辑：

require 'connect.php';

// 用方案二的SQL为例
$query = "
SELECT 
    all_dates.date,
    COALESCE(s.sales_total, 0) AS sales_total,
    COALESCE(p.purchase_total, 0) AS purchase_total
FROM 
    (SELECT date FROM sales UNION SELECT date FROM purchase) all_dates
LEFT JOIN 
    (SELECT date, SUM(price) AS sales_total FROM sales GROUP BY date) s 
    ON all_dates.date = s.date
LEFT JOIN 
    (SELECT date, SUM(price) AS purchase_total FROM purchase GROUP BY date) p 
    ON all_dates.date = p.date
ORDER BY 
    date;
";

$result = mysqli_query($conn, $query);

if(mysqli_num_rows($result) > 0){
    $dates = [];
    $sales = [];
    $purchases = [];
    while($row = mysqli_fetch_assoc($result)){
        $dates[] = $row['date'];
        $sales[] = $row['sales_total'];
        $purchases[] = $row['purchase_total'];
    }
    $res = [
        'dates' => $dates,
        'sales_total' => $sales,
        'purchase_total' => $purchases
    ];
    echo json_encode($res);
} else {
    echo "ERROR";
}

这里用COALESCE把NULL值替换成0，保证某天没有销售或采购时，金额显示为0而不是空值，更符合前端展示需求。

内容的提问来源于stack exchange，提问作者Daniel