add_receipt_container_detail_status.sql
1.86 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
-- ============================================
-- 入库组盘详情表新增 status 字段
-- 用于按入库单ID和组盘详情状态进行ERP回传
-- ============================================
-- 添加 status 字段到 receipt_container_detail 表
ALTER TABLE receipt_container_detail ADD status INT NULL;
-- 为现有数据设置初始状态值（基于现有组盘头的状态同步）
-- 注意：这个脚本需要在测试环境执行，生产环境执行前请先备份数据
-- 更新现有数据：根据 receipt_container_id 关联 receipt_container_header 表获取状态
UPDATE rcd
SET rcd.status = rch.status
FROM receipt_container_detail rcd
INNER JOIN receipt_container_header rch ON rcd.receipt_container_id = rch.id
WHERE rcd.status IS NULL;
-- 如果没有关联到组盘头的数据，设置默认状态为 10（组盘状态）
UPDATE receipt_container_detail
SET status = 10
WHERE status IS NULL;
-- ============================================
-- 查询验证
-- ============================================
-- 查看表结构
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'receipt_container_detail'
AND COLUMN_NAME = 'status';
-- 查看状态分布
SELECT
CASE WHEN status IS NULL THEN 'NULL'
WHEN status = 0 THEN '0-新建'
WHEN status = 10 THEN '10-组盘'
WHEN status = 20 THEN '20-入库完成'
WHEN status = 50 THEN '50-已回传'
ELSE CAST(status AS VARCHAR)
END as status_desc,
COUNT(*) as cnt
FROM receipt_container_detail
GROUP BY CASE WHEN status IS NULL THEN 'NULL'
WHEN status = 0 THEN '0-新建'
WHEN status = 10 THEN '10-组盘'
WHEN status = 20 THEN '20-入库完成'
WHEN status = 50 THEN '50-已回传'
ELSE CAST(status AS VARCHAR)
END
ORDER BY ISNULL(status, 999);