74 lines
1.8 KiB
SQL
74 lines
1.8 KiB
SQL
-- 修复订单状态不一致的SQL脚本
|
||
|
||
-- 1. 修复：已派单但状态不是1的订单
|
||
UPDATE `order`
|
||
SET status = 1
|
||
WHERE teacher_id IS NOT NULL
|
||
AND status != 1
|
||
AND pay_status = 1
|
||
AND deleted = 0;
|
||
|
||
-- 2. 修复：未派单但状态不是0的订单（排除已完成和已取消）
|
||
UPDATE `order`
|
||
SET status = 0
|
||
WHERE teacher_id IS NULL
|
||
AND status NOT IN (0, 4, 5) -- 排除待派单、已完成和已取消
|
||
AND pay_status = 1
|
||
AND deleted = 0;
|
||
|
||
-- 3. 检查修复结果 - 统计各状态订单数量
|
||
SELECT
|
||
status,
|
||
COUNT(*) as count,
|
||
CASE
|
||
WHEN status = 0 THEN '待派单'
|
||
WHEN status = 1 THEN '已派单/待接单'
|
||
WHEN status = 2 THEN '待服务'
|
||
WHEN status = 3 THEN '服务中'
|
||
WHEN status = 4 THEN '已完成'
|
||
WHEN status = 5 THEN '已取消'
|
||
ELSE '未知状态'
|
||
END as status_name
|
||
FROM `order`
|
||
WHERE pay_status = 1 AND deleted = 0
|
||
GROUP BY status
|
||
ORDER BY status;
|
||
|
||
-- 4. 检查是否还有异常订单
|
||
SELECT
|
||
id,
|
||
order_no,
|
||
teacher_id,
|
||
status,
|
||
pay_status,
|
||
CASE
|
||
WHEN teacher_id IS NULL AND status NOT IN (0, 4, 5) THEN '❌ 异常：未派单但状态不是0/4/5'
|
||
WHEN teacher_id IS NOT NULL AND status = 0 THEN '❌ 异常：已派单但状态是0'
|
||
ELSE '✅ 正常'
|
||
END as check_result
|
||
FROM `order`
|
||
WHERE pay_status = 1 AND deleted = 0
|
||
AND (
|
||
(teacher_id IS NULL AND status NOT IN (0, 4, 5)) OR
|
||
(teacher_id IS NOT NULL AND status = 0)
|
||
)
|
||
ORDER BY create_time DESC
|
||
LIMIT 50;
|
||
|
||
-- 5. 查看修复后的待派单订单列表
|
||
SELECT
|
||
id,
|
||
order_no,
|
||
user_id,
|
||
teacher_id,
|
||
status,
|
||
pay_status,
|
||
create_time
|
||
FROM `order`
|
||
WHERE pay_status = 1
|
||
AND status = 0
|
||
AND teacher_id IS NULL
|
||
AND deleted = 0
|
||
ORDER BY create_time DESC
|
||
LIMIT 20;
|