76 lines
2.6 KiB
SQL
76 lines
2.6 KiB
SQL
-- 检查样品状态与工单的一致性
|
|
-- 用于诊断"删除工单后仍提示已有工单"的问题
|
|
|
|
-- 1. 查看样品状态分布
|
|
SELECT
|
|
'样品状态分布' AS 检查项,
|
|
status AS 状态值,
|
|
CASE
|
|
WHEN status = '0' THEN '待测试'
|
|
WHEN status = '1' THEN '测试中'
|
|
WHEN status = '2' THEN '已完成'
|
|
ELSE '未知'
|
|
END AS 状态名称,
|
|
COUNT(*) AS 数量
|
|
FROM warehouse_sample
|
|
GROUP BY status
|
|
ORDER BY status;
|
|
|
|
-- 2. 查看工单数量
|
|
SELECT
|
|
'工单数量' AS 检查项,
|
|
COUNT(*) AS 总数,
|
|
COUNT(CASE WHEN order_id IS NULL THEN 1 END) AS 未汇总数量,
|
|
COUNT(CASE WHEN order_id IS NOT NULL THEN 1 END) AS 已汇总数量
|
|
FROM test_work_order;
|
|
|
|
-- 3. 查找状态为"测试中"但没有工单的样品（数据不一致）
|
|
SELECT
|
|
'不一致的样品' AS 检查项,
|
|
s.sample_id AS 样品ID,
|
|
s.sample_sn AS 样品SN,
|
|
s.status AS 状态,
|
|
CASE
|
|
WHEN s.status = '0' THEN '待测试'
|
|
WHEN s.status = '1' THEN '测试中'
|
|
WHEN s.status = '2' THEN '已完成'
|
|
ELSE '未知'
|
|
END AS 状态名称,
|
|
COUNT(wo.id) AS 工单数量
|
|
FROM warehouse_sample s
|
|
LEFT JOIN test_eut te ON s.sample_sn = te.sn
|
|
LEFT JOIN test_work_order wo ON te.id = wo.test_eut_id AND wo.order_id IS NULL
|
|
WHERE s.status != '0' -- 非待测试状态
|
|
GROUP BY s.sample_id, s.sample_sn, s.status
|
|
HAVING COUNT(wo.id) = 0;
|
|
|
|
-- 4. 查看最近的样品和工单关联情况
|
|
SELECT
|
|
'最近样品工单关联' AS 检查项,
|
|
s.sample_id AS 样品ID,
|
|
s.sample_sn AS 样品SN,
|
|
s.status AS 样品状态,
|
|
te.id AS TestEutID,
|
|
COUNT(wo.id) AS 工单数量,
|
|
GROUP_CONCAT(wo.id) AS 工单ID列表
|
|
FROM warehouse_sample s
|
|
LEFT JOIN test_eut te ON s.sample_sn = te.sn
|
|
LEFT JOIN test_work_order wo ON te.id = wo.test_eut_id AND wo.order_id IS NULL
|
|
GROUP BY s.sample_id, s.sample_sn, s.status, te.id
|
|
ORDER BY s.sample_id DESC
|
|
LIMIT 10;
|
|
|
|
-- 5. 统计结果
|
|
SELECT
|
|
'统计结果' AS 检查项,
|
|
(SELECT COUNT(*) FROM warehouse_sample) AS 样品总数,
|
|
(SELECT COUNT(*) FROM warehouse_sample WHERE status = '0') AS 待测试样品数,
|
|
(SELECT COUNT(*) FROM warehouse_sample WHERE status = '1') AS 测试中样品数,
|
|
(SELECT COUNT(*) FROM warehouse_sample WHERE status = '2') AS 已完成样品数,
|
|
(SELECT COUNT(*) FROM test_work_order WHERE order_id IS NULL) AS 未汇总工单数,
|
|
(SELECT COUNT(DISTINCT s.sample_id)
|
|
FROM warehouse_sample s
|
|
LEFT JOIN test_eut te ON s.sample_sn = te.sn
|
|
LEFT JOIN test_work_order wo ON te.id = wo.test_eut_id AND wo.order_id IS NULL
|
|
WHERE s.status != '0' AND wo.id IS NULL) AS 不一致样品数;
|