MySQL中基于Delivery Date与Estimated Delivery Date两个日期字段统计订单记录总数的实现方案

先帮你梳理下核心问题：你之前用UNION的方式会把两个日期字段的统计结果分开返回，但我们需要的是将Delivery Date和Estimated Delivery Date中的所有日期统一作为统计维度，合并计算同一日期下的记录数总和。下面是具体的实现方案：

1. 正确的SQL查询逻辑 #

我们需要先把两个日期字段的所有有效日期拆成单独的行，再按日期分组统计总数。用UNION ALL合并两个字段的数据集（比UNION高效，无需去重），再进行聚合统计：

SELECT 
    date AS ShipDate,
    COUNT(DISTINCT company_entity) AS total_count
FROM (
    -- 提取Delivery Date的有效记录
    SELECT deliverydate AS date, company_entity 
    FROM myorders 
    WHERE deliverydate >= ?
    UNION ALL
    -- 提取Estimated Delivery Date的有效记录
    SELECT deliverydate2 AS date, company_entity 
    FROM myorders 
    WHERE deliverydate2 >= ?
) AS combined_dates
WHERE date IS NOT NULL -- 过滤空日期的无效记录
GROUP BY date
ORDER BY date;

逻辑说明 #

• 内层查询把两个日期字段的date和对应company_entity全部列出，形成统一的日期数据集
• 外层查询按date分组，统计每个日期下的唯一company_entity总数（和你原逻辑一致，避免重复统计同一家公司）
• 用?作为参数占位符，避免SQL注入风险

2. 修改后的PHP代码实现 #

结合预处理语句（替代直接拼接SQL字符串），完整代码如下：

// 定义预处理SQL语句
$query = "
    SELECT 
        date AS ShipDate,
        COUNT(DISTINCT company_entity) AS total_count
    FROM (
        SELECT deliverydate AS date, company_entity 
        FROM myorders 
        WHERE deliverydate >= ?
        UNION ALL
        SELECT deliverydate2 AS date, company_entity 
        FROM myorders 
        WHERE deliverydate2 >= ?
    ) AS combined_dates
    WHERE date IS NOT NULL
    GROUP BY date
    ORDER BY date;
";

// 初始化预处理语句
if ($stmt = $mysqli->prepare($query)) {
    // 绑定两个日期参数（s代表字符串类型，适配日期格式）
    $stmt->bind_param("ss", $current_date, $current_date);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $arr = [];
    while ($row = $result->fetch_assoc()) {
        $total = $row["total_count"];
        $shipDate = $row["ShipDate"];
        $arr[] = [
            'title' => $total . " (Total Deliveries)",
            'url' => 'list_of_orders_by_date.php?dt=' . substr($shipDate, 0, 10),
            'start' => substr($shipDate, 0, 10)
        ];
    }
    
    echo json_encode($arr);
    
    // 释放资源
    $result->free();
    $stmt->close();
}

3. 注意事项 #

• 如果你的Estimated Delivery Date字段名不是deliverydate2，请替换为实际字段名
• COUNT(DISTINCT company_entity)确保同一家公司的记录不会被重复统计，和你原代码逻辑保持一致
• 预处理语句彻底避免了SQL注入风险，比直接拼接字符串更安全
• 过滤date IS NOT NULL是为了排除日期字段为空的无效记录

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