111 lines
3.0 KiB
SQL
111 lines
3.0 KiB
SQL
-- 检查各个测试步骤的工单数量
|
||
-- 用于诊断"测试中"页面为什么是空的
|
||
|
||
-- 1. 统计各个测试步骤的工单数量
|
||
SELECT
|
||
'1. 各测试步骤工单统计' AS step;
|
||
SELECT
|
||
test_step,
|
||
CASE
|
||
WHEN test_step = 0 THEN '待领取'
|
||
WHEN test_step = 1 THEN '测试中'
|
||
WHEN test_step = 2 THEN '一审中'
|
||
WHEN test_step = 3 THEN '二审中'
|
||
WHEN test_step = 4 THEN '三审中'
|
||
WHEN test_step = 5 THEN '已完成'
|
||
ELSE '未知状态'
|
||
END AS step_name,
|
||
COUNT(*) AS count,
|
||
COUNT(CASE WHEN order_id IS NULL THEN 1 END) AS unlinked_count,
|
||
COUNT(CASE WHEN order_id IS NOT NULL THEN 1 END) AS linked_count
|
||
FROM test_work_order
|
||
GROUP BY test_step
|
||
ORDER BY test_step;
|
||
|
||
-- 2. 查看所有未关联订单的工单（应该显示在列表中的）
|
||
SELECT
|
||
'2. 所有未关联订单的工单' AS step;
|
||
SELECT
|
||
id,
|
||
name,
|
||
test_step,
|
||
CASE
|
||
WHEN test_step = 0 THEN '待领取'
|
||
WHEN test_step = 1 THEN '测试中'
|
||
WHEN test_step = 2 THEN '一审中'
|
||
WHEN test_step = 3 THEN '二审中'
|
||
WHEN test_step = 4 THEN '三审中'
|
||
WHEN test_step = 5 THEN '已完成'
|
||
ELSE '未知状态'
|
||
END AS step_name,
|
||
order_id,
|
||
create_time
|
||
FROM test_work_order
|
||
WHERE order_id IS NULL
|
||
ORDER BY test_step, id DESC;
|
||
|
||
-- 3. 查看 test_step = 1（测试中）的工单
|
||
SELECT
|
||
'3. 测试中的工单（test_step = 1）' AS step;
|
||
SELECT
|
||
id,
|
||
batch_id,
|
||
batch_name,
|
||
name,
|
||
test_step,
|
||
test_status,
|
||
order_id,
|
||
tester_id,
|
||
create_time
|
||
FROM test_work_order
|
||
WHERE test_step = 1 AND order_id IS NULL
|
||
ORDER BY id DESC;
|
||
|
||
-- 4. 查看最近创建的工单的 test_step 值
|
||
SELECT
|
||
'4. 最近创建的工单' AS step;
|
||
SELECT
|
||
id,
|
||
name,
|
||
test_step,
|
||
CASE
|
||
WHEN test_step = 0 THEN '待领取'
|
||
WHEN test_step = 1 THEN '测试中'
|
||
WHEN test_step = 2 THEN '一审中'
|
||
WHEN test_step = 3 THEN '二审中'
|
||
WHEN test_step = 4 THEN '三审中'
|
||
WHEN test_step = 5 THEN '已完成'
|
||
WHEN test_step IS NULL THEN 'NULL'
|
||
ELSE '未知状态'
|
||
END AS step_name,
|
||
order_id,
|
||
create_time
|
||
FROM test_work_order
|
||
ORDER BY id DESC
|
||
LIMIT 20;
|
||
|
||
-- 5. 检查是否所有工单的 test_step 都是 NULL 或 0
|
||
SELECT
|
||
'5. test_step 为 NULL 或 0 的工单' AS step;
|
||
SELECT
|
||
COUNT(*) AS total_count,
|
||
COUNT(CASE WHEN test_step IS NULL THEN 1 END) AS null_count,
|
||
COUNT(CASE WHEN test_step = 0 THEN 1 END) AS zero_count,
|
||
COUNT(CASE WHEN test_step > 0 THEN 1 END) AS greater_than_zero_count
|
||
FROM test_work_order
|
||
WHERE order_id IS NULL;
|
||
|
||
-- 6. 查看是否有工单的 test_step 字段有问题
|
||
SELECT
|
||
'6. test_step 字段定义' AS step;
|
||
SELECT
|
||
COLUMN_NAME,
|
||
COLUMN_TYPE,
|
||
IS_NULLABLE,
|
||
COLUMN_DEFAULT,
|
||
COLUMN_COMMENT
|
||
FROM INFORMATION_SCHEMA.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'test_work_order'
|
||
AND COLUMN_NAME = 'test_step';
|