# 到货信息统计 MySQL 语句

## 1. 基本统计语句

```sql
SELECT 
    COALESCE(l.BillOfLadingNumber, l.MasterPackageNumber, 'Unknown') 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 = COALESCE(l.BillOfLadingNumber, l.MasterPackageNumber)) AS ArrivalTime,
    MAX(l.BillOfLadingNumber) AS BillOfLadingNumber,
    MAX(l.MasterPackageNumber) AS 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.CreatedAt >= '2026-01-01' OR '2026-01-01' IS NULL)
    AND (l.CreatedAt <= '2026-12-31' OR '2026-12-31' IS NULL)
GROUP BY 
    COALESCE(l.BillOfLadingNumber, l.MasterPackageNumber, 'Unknown')
ORDER BY 
    l.CreatedAt DESC;
```

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

```sql
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 
    COALESCE(l.BillOfLadingNumber, l.MasterPackageNumber, 'Unknown') 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,
    MAX(l.BillOfLadingNumber) AS BillOfLadingNumber,
    MAX(l.MasterPackageNumber) AS 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.CreatedAt >= '2026-01-01' OR '2026-01-01' IS NULL)
    AND (l.CreatedAt <= '2026-12-31' OR '2026-12-31' IS NULL)
GROUP BY 
    COALESCE(l.BillOfLadingNumber, l.MasterPackageNumber, 'Unknown')
ORDER BY 
    l.CreatedAt DESC;
```

## 3. 实现说明

### 3.1 分组逻辑

使用 `COALESCE` 函数实现提单号优先的分组逻辑：
- 当 `BillOfLadingNumber` 不为 NULL 时，使用 `BillOfLadingNumber` 作为分组依据
- 当 `BillOfLadingNumber` 为 NULL 时，使用 `MasterPackageNumber` 作为分组依据
- 当两者都为 NULL 时，使用 'Unknown' 作为分组依据

### 3.2 统计计算

- **到货订单数量**：使用 `COUNT(*)` 统计每个分组的记录数
- **无标签数据数量**：使用 `SUM(CASE WHEN l.Label IS NULL OR l.Label = '' THEN 1 ELSE 0 END)` 统计
- **已有标签订单数**：使用 `SUM(CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN 1 ELSE 0 END)` 统计
- **已有标签率**：计算已有标签订单数占总订单数的百分比
- **换单完成数量**：统计有扫描记录且结果为 0 的记录数
- **未换单完成数量**：总订单数减去换单完成数量
- **到货时间**：从到货交接单表中获取最早的收货时间

### 3.3 过滤条件

- **客户ID过滤**：可根据需要修改客户ID值
- **日期范围过滤**：可根据需要修改日期范围

## 4. 性能优化建议

1. **索引优化**：
   - 在 `label_replace_requests` 表上添加以下索引：
     - `(CustomerId, CreatedAt)`
     - `(BillOfLadingNumber)`
     - `(MasterPackageNumber)`
   - 在 `label_scan_history` 表上添加索引：
     - `(NeutralWaybillNumber, Result)`
   - 在 `arrival_handover_forms` 表上添加索引：
     - `(HandoverNumber, ReceiptTime)`

2. **查询优化**：
   - 使用 CTE (Common Table Expressions) 减少重复子查询
   - 避免在 GROUP BY 子句中使用复杂表达式
   - 合理使用 JOIN 替代子查询

3. **数据量控制**：
   - 考虑添加分页功能，避免一次性返回大量数据
   - 对于历史数据，可以考虑归档策略

## 5. 使用说明

1. **参数调整**：
   - 客户ID：修改 `l.CustomerId = 1` 中的 1 为实际客户ID
   - 日期范围：修改 `'2026-01-01'` 和 `'2026-12-31'` 为实际日期范围

2. **结果解释**：
   - `Key` 列：表示分组依据，可能是提单号、主包号或 'Unknown'
   - 其他列：表示各统计指标

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