# 数据看板查询 MySQL 语句

## 1. 提单号预报查询

### 1.1 基本查询语句

```sql
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 = :billOfLadingNumber OR :billOfLadingNumber IS NULL)
    -- 大箱号过滤
    AND (l.MasterPackageNumber = :masterPackageNumber OR :masterPackageNumber IS NULL)
    -- 日期范围过滤
    AND (l.CreatedAt >= '2026-01-01' OR '2026-01-01' IS NULL)
    AND (l.CreatedAt <= :endDate OR :endDate IS NULL)
GROUP BY 
    l.BillOfLadingNumber
ORDER BY 
    l.CreatedAt DESC;
```

### 1.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 
    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 = :billOfLadingNumber OR :billOfLadingNumber IS NULL)
    -- 大箱号过滤
    AND (l.MasterPackageNumber = :masterPackageNumber OR :masterPackageNumber IS NULL)
    -- 日期范围过滤
    AND (l.CreatedAt >= '2026-01-01' OR '2026-01-01' IS NULL)
    AND (l.CreatedAt <= :endDate OR :endDate IS NULL)
GROUP BY 
    l.BillOfLadingNumber
ORDER BY 
    l.CreatedAt DESC;
```

## 2. 大箱号预报查询

### 2.1 基本查询语句

```sql
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 = :billOfLadingNumber OR :billOfLadingNumber IS NULL)
    -- 大箱号过滤
    AND (l.MasterPackageNumber = :masterPackageNumber OR :masterPackageNumber IS NULL)
    -- 日期范围过滤
    AND (l.CreatedAt >= '2026-01-01' OR '2026-01-01' IS NULL)
    AND (l.CreatedAt <= :endDate OR :endDate IS NULL)
GROUP BY 
    l.MasterPackageNumber
ORDER BY 
    l.CreatedAt DESC;
```

### 2.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 
    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 = :billOfLadingNumber OR :billOfLadingNumber IS NULL)
    -- 大箱号过滤
    AND (l.MasterPackageNumber = :masterPackageNumber OR :masterPackageNumber IS NULL)
    -- 日期范围过滤
    AND (l.CreatedAt >= '2026-01-01' OR '2026-01-01' IS NULL)
    AND (l.CreatedAt <= :endDate OR :endDate IS NULL)
GROUP BY 
    l.MasterPackageNumber
ORDER BY 
    l.CreatedAt DESC;
```

## 3. 参数说明

| 参数 | 类型 | 描述 |
|------|------|------|
| customerId | INT | 客户ID |
| billOfLadingNumber | VARCHAR(100) | 提单号 |
| masterPackageNumber | VARCHAR(100) | 大箱号 |
| startDate | DATETIME | 开始日期 |
| endDate | DATETIME | 结束日期 |

## 4. 性能优化建议

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

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

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

4. **执行计划分析**：
   - 使用 `EXPLAIN` 分析查询执行计划
   - 确保索引被正确使用
   - 优化 JOIN 顺序

## 5. 注意事项

- 以上语句假设数据库表结构与当前代码一致
- 实际使用时需要替换参数占位符（如 `1`）为实际值
- 对于大量数据，建议使用优化版本的查询语句
- 可以根据实际情况调整索引策略