146 lines
3.5 KiB
SQL
146 lines
3.5 KiB
SQL
-- 检查订单派单状态
|
||
|
||
-- 1. 查看所有订单的状态分布
|
||
SELECT
|
||
status,
|
||
pay_status,
|
||
CASE
|
||
WHEN teacher_id IS NULL THEN '未分配'
|
||
ELSE '已分配'
|
||
END as teacher_status,
|
||
COUNT(*) as count
|
||
FROM `order`
|
||
WHERE deleted = 0
|
||
GROUP BY status, pay_status, teacher_status
|
||
ORDER BY status, pay_status;
|
||
|
||
-- 2. 查看待派单订单（正确的逻辑：已支付但未分配陪伴员）
|
||
SELECT
|
||
id,
|
||
order_no,
|
||
status,
|
||
pay_status,
|
||
teacher_id,
|
||
user_id,
|
||
total_amount,
|
||
service_date,
|
||
create_time,
|
||
update_time
|
||
FROM `order`
|
||
WHERE pay_status = 1
|
||
AND teacher_id IS NULL
|
||
AND deleted = 0
|
||
ORDER BY create_time DESC;
|
||
|
||
-- 3. 查看已派单订单（已分配陪伴员）
|
||
SELECT
|
||
o.id,
|
||
o.order_no,
|
||
o.status,
|
||
o.pay_status,
|
||
o.teacher_id,
|
||
t.name as teacher_name,
|
||
t.real_name as teacher_real_name,
|
||
o.user_id,
|
||
o.total_amount,
|
||
o.service_date,
|
||
o.create_time,
|
||
o.update_time
|
||
FROM `order` o
|
||
LEFT JOIN teacher t ON o.teacher_id = t.id
|
||
WHERE o.teacher_id IS NOT NULL
|
||
AND o.deleted = 0
|
||
ORDER BY o.update_time DESC
|
||
LIMIT 20;
|
||
|
||
-- 4. 查看最近派单的订单（按更新时间排序）
|
||
SELECT
|
||
o.id,
|
||
o.order_no,
|
||
o.status,
|
||
o.teacher_id,
|
||
t.name as teacher_name,
|
||
o.update_time,
|
||
TIMESTAMPDIFF(MINUTE, o.create_time, o.update_time) as response_minutes
|
||
FROM `order` o
|
||
LEFT JOIN teacher t ON o.teacher_id = t.id
|
||
WHERE o.teacher_id IS NOT NULL
|
||
AND o.deleted = 0
|
||
ORDER BY o.update_time DESC
|
||
LIMIT 10;
|
||
|
||
-- 5. 检查派单前后状态变化（需要先记录订单ID）
|
||
-- 使用方法：
|
||
-- 步骤1：派单前运行此查询，记录订单状态
|
||
-- 步骤2：执行派单操作
|
||
-- 步骤3：再次运行此查询，对比状态变化
|
||
SELECT
|
||
id,
|
||
order_no,
|
||
status,
|
||
pay_status,
|
||
teacher_id,
|
||
update_time,
|
||
CASE
|
||
WHEN status = 0 AND teacher_id IS NULL THEN '待派单'
|
||
WHEN status = 1 AND teacher_id IS NOT NULL THEN '已派单'
|
||
WHEN status = 2 THEN '服务中'
|
||
WHEN status = 3 THEN '已完成'
|
||
WHEN status = 4 THEN '已取消'
|
||
ELSE '未知状态'
|
||
END as status_desc
|
||
FROM `order`
|
||
WHERE id = <订单ID>; -- 替换为实际订单ID
|
||
|
||
-- 6. 统计各状态订单数量
|
||
SELECT
|
||
CASE
|
||
WHEN status = 0 AND teacher_id IS NULL AND pay_status = 1 THEN '待派单'
|
||
WHEN status = 0 AND pay_status = 0 THEN '待支付'
|
||
WHEN status = 1 THEN '已派单/待接单'
|
||
WHEN status = 2 THEN '服务中'
|
||
WHEN status = 3 THEN '已完成'
|
||
WHEN status = 4 THEN '已取消'
|
||
ELSE '其他'
|
||
END as order_status,
|
||
COUNT(*) as count
|
||
FROM `order`
|
||
WHERE deleted = 0
|
||
GROUP BY order_status
|
||
ORDER BY
|
||
CASE order_status
|
||
WHEN '待支付' THEN 1
|
||
WHEN '待派单' THEN 2
|
||
WHEN '已派单/待接单' THEN 3
|
||
WHEN '服务中' THEN 4
|
||
WHEN '已完成' THEN 5
|
||
WHEN '已取消' THEN 6
|
||
ELSE 7
|
||
END;
|
||
|
||
-- 7. 检查陪伴员的userId映射
|
||
SELECT
|
||
t.id as teacher_id,
|
||
t.user_id,
|
||
t.name,
|
||
t.real_name,
|
||
t.audit_status,
|
||
COUNT(o.id) as order_count
|
||
FROM teacher t
|
||
LEFT JOIN `order` o ON t.id = o.teacher_id AND o.deleted = 0
|
||
WHERE t.audit_status = 1
|
||
GROUP BY t.id, t.user_id, t.name, t.real_name, t.audit_status
|
||
ORDER BY order_count DESC;
|
||
|
||
-- 8. 检查是否有陪伴员userId为空
|
||
SELECT
|
||
id as teacher_id,
|
||
user_id,
|
||
name,
|
||
real_name,
|
||
phone,
|
||
audit_status
|
||
FROM teacher
|
||
WHERE audit_status = 1
|
||
AND user_id IS NULL;
|