262 lines
8.6 KiB
Markdown
262 lines
8.6 KiB
Markdown
# 数据看板查询 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`）为实际值
|
||
- 对于大量数据，建议使用优化版本的查询语句
|
||
- 可以根据实际情况调整索引策略 |