LabelChange-server/.trae/specs/dashboard_query_sql/mysql_statements_fixed.md

数据看板查询 MySQL 语句（修复版）

1. 提单号预报查询

1.1 基本查询语句

SELECT 
    l.BillOfLadingNumber AS `Key`,
    c.CustomerCode AS CustomerCode,
    COUNT(*) AS ArrivalOrderCount,
    SUM(CASE WHEN l.Label IS NULL OR l.Label = '' THEN 1 ELSE 0 END) AS NoLabelDataCount,
    SUM(CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN 1 ELSE 0 END) AS LabeledOrderCount,
    CONCAT(ROUND((SUM(CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN 1 ELSE 0 END) / COUNT(*)) * 100, 2), '%') AS LabelRate,
    (SELECT COUNT(*) 
     FROM label_scan_history s 
     WHERE s.NeutralWaybillNumber = l.NeutralWaybillNumber 
     AND s.Result = 0) AS ReplaceCompletedCount,
    COUNT(*) - (SELECT COUNT(*) 
                FROM label_scan_history s 
                WHERE s.NeutralWaybillNumber = l.NeutralWaybillNumber 
                AND s.Result = 0) AS ReplacePendingCount,
    (SELECT MIN(h.ReceiptTime) 
     FROM arrival_handover_forms h 
     WHERE h.HandoverNumber = l.BillOfLadingNumber 
     OR h.HandoverNumber = l.MasterPackageNumber) AS ArrivalTime,
    l.BillOfLadingNumber,
    l.MasterPackageNumber
FROM 
    label_replace_requests l
LEFT JOIN 
    customers c ON l.CustomerId = c.Id
WHERE 
    1=1
    -- 客户ID过滤
    AND (l.CustomerId = 1 OR 1 IS NULL)
    -- 提单号过滤
    AND (l.BillOfLadingNumber = 'BOL123456' OR 'BOL123456' IS NULL)
    -- 大箱号过滤
    AND (l.MasterPackageNumber = 'MP123456' OR 'MP123456' IS NULL)
    -- 日期范围过滤
    AND (l.CreatedAt >= '2026-01-01' OR '2026-01-01' IS NULL)
    AND (l.CreatedAt <= '2026-12-31' OR '2026-12-31' IS NULL)
GROUP BY 
    l.BillOfLadingNumber
ORDER BY 
    l.CreatedAt DESC;

1.2 优化版本（使用子查询优化扫描记录统计）

WITH scan_summary AS (
    SELECT 
        NeutralWaybillNumber,
        COUNT(*) AS ReturnedLabelCount
    FROM 
        label_scan_history
    WHERE 
        Result = 0
    GROUP BY 
        NeutralWaybillNumber
),
arrival_summary AS (
    SELECT 
        HandoverNumber,
        MIN(ReceiptTime) AS MinReceiptTime
    FROM 
        arrival_handover_forms
    GROUP BY 
        HandoverNumber
)
SELECT 
    l.BillOfLadingNumber AS `Key`,
    c.CustomerCode AS CustomerCode,
    COUNT(*) AS ArrivalOrderCount,
    SUM(CASE WHEN l.Label IS NULL OR l.Label = '' THEN 1 ELSE 0 END) AS NoLabelDataCount,
    SUM(CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN 1 ELSE 0 END) AS LabeledOrderCount,
    CONCAT(ROUND((SUM(CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN 1 ELSE 0 END) / COUNT(*)) * 100, 2), '%') AS LabelRate,
    COALESCE(SUM(s.ReturnedLabelCount), 0) AS ReplaceCompletedCount,
    COUNT(*) - COALESCE(SUM(s.ReturnedLabelCount), 0) AS ReplacePendingCount,
    COALESCE(a.MinReceiptTime, b.MinReceiptTime) AS ArrivalTime,
    l.BillOfLadingNumber,
    l.MasterPackageNumber
FROM 
    label_replace_requests l
LEFT JOIN 
    customers c ON l.CustomerId = c.Id
LEFT JOIN 
    scan_summary s ON l.NeutralWaybillNumber = s.NeutralWaybillNumber
LEFT JOIN 
    arrival_summary a ON l.BillOfLadingNumber = a.HandoverNumber
LEFT JOIN 
    arrival_summary b ON l.MasterPackageNumber = b.HandoverNumber
WHERE 
    1=1
    -- 客户ID过滤
    AND (l.CustomerId = 1 OR 1 IS NULL)
    -- 提单号过滤
    AND (l.BillOfLadingNumber = 'BOL123456' OR 'BOL123456' IS NULL)
    -- 大箱号过滤
    AND (l.MasterPackageNumber = 'MP123456' OR 'MP123456' IS NULL)
    -- 日期范围过滤
    AND (l.CreatedAt >= '2026-01-01' OR '2026-01-01' IS NULL)
    AND (l.CreatedAt <= '2026-12-31' OR '2026-12-31' IS NULL)
GROUP BY 
    l.BillOfLadingNumber
ORDER BY 
    l.CreatedAt DESC;

2. 大箱号预报查询

2.1 基本查询语句

SELECT 
    l.MasterPackageNumber AS `Key`,
    c.CustomerCode AS CustomerCode,
    COUNT(*) AS ArrivalOrderCount,
    SUM(CASE WHEN l.Label IS NULL OR l.Label = '' THEN 1 ELSE 0 END) AS NoLabelDataCount,
    SUM(CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN 1 ELSE 0 END) AS LabeledOrderCount,
    CONCAT(ROUND((SUM(CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN 1 ELSE 0 END) / COUNT(*)) * 100, 2), '%') AS LabelRate,
    (SELECT COUNT(*) 
     FROM label_scan_history s 
     WHERE s.NeutralWaybillNumber = l.NeutralWaybillNumber 
     AND s.Result = 0) AS ReplaceCompletedCount,
    COUNT(*) - (SELECT COUNT(*) 
                FROM label_scan_history s 
                WHERE s.NeutralWaybillNumber = l.NeutralWaybillNumber 
                AND s.Result = 0) AS ReplacePendingCount,
    (SELECT MIN(h.ReceiptTime) 
     FROM arrival_handover_forms h 
     WHERE h.HandoverNumber = l.BillOfLadingNumber 
     OR h.HandoverNumber = l.MasterPackageNumber) AS ArrivalTime,
    l.BillOfLadingNumber,
    l.MasterPackageNumber
FROM 
    label_replace_requests l
LEFT JOIN 
    customers c ON l.CustomerId = c.Id
WHERE 
    1=1
    -- 客户ID过滤
    AND (l.CustomerId = 1 OR 1 IS NULL)
    -- 提单号过滤
    AND (l.BillOfLadingNumber = 'BOL123456' OR 'BOL123456' IS NULL)
    -- 大箱号过滤
    AND (l.MasterPackageNumber = 'MP123456' OR 'MP123456' IS NULL)
    -- 日期范围过滤
    AND (l.CreatedAt >= '2026-01-01' OR '2026-01-01' IS NULL)
    AND (l.CreatedAt <= '2026-12-31' OR '2026-12-31' IS NULL)
GROUP BY 
    l.MasterPackageNumber
ORDER BY 
    l.CreatedAt DESC;

2.2 优化版本（使用子查询优化扫描记录统计）

WITH scan_summary AS (
    SELECT 
        NeutralWaybillNumber,
        COUNT(*) AS ReturnedLabelCount
    FROM 
        label_scan_history
    WHERE 
        Result = 0
    GROUP BY 
        NeutralWaybillNumber
),
arrival_summary AS (
    SELECT 
        HandoverNumber,
        MIN(ReceiptTime) AS MinReceiptTime
    FROM 
        arrival_handover_forms
    GROUP BY 
        HandoverNumber
)
SELECT 
    l.MasterPackageNumber AS `Key`,
    c.CustomerCode AS CustomerCode,
    COUNT(*) AS ArrivalOrderCount,
    SUM(CASE WHEN l.Label IS NULL OR l.Label = '' THEN 1 ELSE 0 END) AS NoLabelDataCount,
    SUM(CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN 1 ELSE 0 END) AS LabeledOrderCount,
    CONCAT(ROUND((SUM(CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN 1 ELSE 0 END) / COUNT(*)) * 100, 2), '%') AS LabelRate,
    COALESCE(SUM(s.ReturnedLabelCount), 0) AS ReplaceCompletedCount,
    COUNT(*) - COALESCE(SUM(s.ReturnedLabelCount), 0) AS ReplacePendingCount,
    COALESCE(a.MinReceiptTime, b.MinReceiptTime) AS ArrivalTime,
    l.BillOfLadingNumber,
    l.MasterPackageNumber
FROM 
    label_replace_requests l
LEFT JOIN 
    customers c ON l.CustomerId = c.Id
LEFT JOIN 
    scan_summary s ON l.NeutralWaybillNumber = s.NeutralWaybillNumber
LEFT JOIN 
    arrival_summary a ON l.BillOfLadingNumber = a.HandoverNumber
LEFT JOIN 
    arrival_summary b ON l.MasterPackageNumber = b.HandoverNumber
WHERE 
    1=1
    -- 客户ID过滤
    AND (l.CustomerId = 1 OR 1 IS NULL)
    -- 提单号过滤
    AND (l.BillOfLadingNumber = 'BOL123456' OR 'BOL123456' IS NULL)
    -- 大箱号过滤
    AND (l.MasterPackageNumber = 'MP123456' OR 'MP123456' IS NULL)
    -- 日期范围过滤
    AND (l.CreatedAt >= '2026-01-01' OR '2026-01-01' IS NULL)
    AND (l.CreatedAt <= '2026-12-31' OR '2026-12-31' IS NULL)
GROUP BY 
    l.MasterPackageNumber
ORDER BY 
    l.CreatedAt DESC;

3. 使用说明

1. 参数说明：

   • 客户ID：1（示例值，根据实际情况修改）
   • 提单号：'BOL123456'（示例值，根据实际情况修改）
   • 大箱号：'MP123456'（示例值，根据实际情况修改）
   • 开始日期：'2026-01-01'（示例值，根据实际情况修改）
   • 结束日期：'2026-12-31'（示例值，根据实际情况修改）

2. 注意事项：

   • Key 是 MySQL 关键字，使用反引号包围
   • 所有字符串参数都使用单引号包围
   • 日期参数使用 'YYYY-MM-DD' 格式
   • 可以根据实际需要修改示例值

3. 性能优化：

   • 对于大量数据，建议使用优化版本的查询语句
   • 确保相关表有合适的索引
   • 可以使用 EXPLAIN 分析查询执行计划