120 lines
3.6 KiB
SQL
120 lines
3.6 KiB
SQL
-- 工单显示问题完整诊断脚本
|
||
-- 执行时间: 2026-01-09
|
||
|
||
-- ============================================
|
||
-- 1. 检查数据库中是否有工单数据
|
||
-- ============================================
|
||
SELECT '1. 检查工单数据' AS step;
|
||
SELECT
|
||
COUNT(*) AS total_workorders,
|
||
COUNT(CASE WHEN order_id IS NULL THEN 1 END) AS unlinked_workorders,
|
||
COUNT(CASE WHEN order_id IS NOT NULL THEN 1 END) AS linked_workorders
|
||
FROM test_work_order;
|
||
|
||
-- ============================================
|
||
-- 2. 查看最近的工单数据（应该显示在列表中的）
|
||
-- ============================================
|
||
SELECT '2. 最近的未关联工单（应该显示）' AS step;
|
||
SELECT
|
||
id,
|
||
batch_id,
|
||
batch_name,
|
||
name,
|
||
test_eut_id,
|
||
order_id,
|
||
create_time
|
||
FROM test_work_order
|
||
WHERE order_id IS NULL
|
||
ORDER BY id DESC
|
||
LIMIT 10;
|
||
|
||
-- ============================================
|
||
-- 3. 检查 name 字段长度是否已修复
|
||
-- ============================================
|
||
SELECT '3. 检查 name 字段定义' AS step;
|
||
SELECT
|
||
COLUMN_NAME,
|
||
COLUMN_TYPE,
|
||
CHARACTER_MAXIMUM_LENGTH,
|
||
IS_NULLABLE,
|
||
COLUMN_COMMENT
|
||
FROM INFORMATION_SCHEMA.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'test_work_order'
|
||
AND COLUMN_NAME = 'name';
|
||
|
||
-- ============================================
|
||
-- 4. 检查 batch_id 字段类型是否正确
|
||
-- ============================================
|
||
SELECT '4. 检查 batch_id 字段定义' AS step;
|
||
SELECT
|
||
COLUMN_NAME,
|
||
COLUMN_TYPE,
|
||
IS_NULLABLE,
|
||
COLUMN_COMMENT
|
||
FROM INFORMATION_SCHEMA.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'test_work_order'
|
||
AND COLUMN_NAME = 'batch_id';
|
||
|
||
-- ============================================
|
||
-- 5. 模拟后端查询（完整的 LEFT JOIN）
|
||
-- ============================================
|
||
SELECT '5. 模拟后端查询' AS step;
|
||
SELECT
|
||
tw.id,
|
||
tw.batch_id,
|
||
tw.batch_name,
|
||
tw.name,
|
||
tw.test_eut_id,
|
||
tw.test_category_id,
|
||
tw.test_step,
|
||
tw.test_status,
|
||
tw.order_id,
|
||
eut.sn AS test_eut_name,
|
||
category.name AS test_category_name,
|
||
creator_user.nick_name AS creator_name
|
||
FROM test_work_order tw
|
||
LEFT JOIN test_eut eut ON tw.test_eut_id = eut.id
|
||
LEFT JOIN test_category category ON tw.test_category_id = category.id
|
||
LEFT JOIN sys_user creator_user ON tw.creator = creator_user.user_id
|
||
WHERE tw.order_id IS NULL
|
||
ORDER BY tw.id DESC
|
||
LIMIT 5;
|
||
|
||
-- ============================================
|
||
-- 6. 检查是否有孤立的工单（关联的数据不存在）
|
||
-- ============================================
|
||
SELECT '6. 检查孤立工单' AS step;
|
||
SELECT
|
||
tw.id,
|
||
tw.name,
|
||
tw.test_eut_id,
|
||
CASE WHEN eut.id IS NULL THEN 'EUT不存在' ELSE 'EUT存在' END AS eut_status,
|
||
tw.test_category_id,
|
||
CASE WHEN category.id IS NULL THEN '类别不存在' ELSE '类别存在' END AS category_status,
|
||
tw.creator,
|
||
CASE WHEN creator_user.user_id IS NULL THEN '创建人不存在' ELSE '创建人存在' END AS creator_status
|
||
FROM test_work_order tw
|
||
LEFT JOIN test_eut eut ON tw.test_eut_id = eut.id
|
||
LEFT JOIN test_category category ON tw.test_category_id = category.id
|
||
LEFT JOIN sys_user creator_user ON tw.creator = creator_user.user_id
|
||
WHERE tw.order_id IS NULL
|
||
ORDER BY tw.id DESC
|
||
LIMIT 5;
|
||
|
||
-- ============================================
|
||
-- 7. 检查表结构完整性
|
||
-- ============================================
|
||
SELECT '7. 检查表结构' 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'
|
||
ORDER BY ORDINAL_POSITION;
|