58 lines
1.3 KiB
MySQL
58 lines
1.3 KiB
MySQL
|
-- 检查工单重复创建问题
|
||
|
|
|
||
|
|
-- 1. 查看订单36-39的工单情况
|
||
|
|
SELECT
|
||
|
|
id,
|
||
|
|
name,
|
||
|
|
test_order_id AS '原始订单ID',
|
||
|
|
order_id AS '生成的订单ID',
|
||
|
|
test_eut_id AS '样品ID',
|
||
|
|
create_time
|
||
|
|
FROM test_work_order
|
||
|
|
WHERE order_id IN (36, 37, 38, 39)
|
||
|
|
ORDER BY order_id, id;
|
||
|
|
|
||
|
|
-- 2. 查看订单36-39是否有直接关联的样品
|
||
|
|
SELECT
|
||
|
|
id,
|
||
|
|
sn,
|
||
|
|
test_order_id AS '订单ID',
|
||
|
|
create_time
|
||
|
|
FROM test_eut
|
||
|
|
WHERE test_order_id IN (36, 37, 38, 39)
|
||
|
|
ORDER BY test_order_id, id;
|
||
|
|
|
||
|
|
-- 3. 查看订单36-39的详细信息
|
||
|
|
SELECT
|
||
|
|
id,
|
||
|
|
name,
|
||
|
|
creator,
|
||
|
|
create_time,
|
||
|
|
total_count AS '工单总数',
|
||
|
|
complate_count AS '完成数',
|
||
|
|
state AS '状态'
|
||
|
|
FROM test_order
|
||
|
|
WHERE id IN (36, 37, 38, 39)
|
||
|
|
ORDER BY id;
|
||
|
|
|
||
|
|
-- 4. 统计每个订单的工单数量
|
||
|
|
SELECT
|
||
|
|
order_id AS '订单ID',
|
||
|
|
COUNT(*) AS '工单数量',
|
||
|
|
GROUP_CONCAT(id) AS '工单ID列表'
|
||
|
|
FROM test_work_order
|
||
|
|
WHERE order_id IN (36, 37, 38, 39)
|
||
|
|
GROUP BY order_id;
|
||
|
|
|
||
|
|
-- 5. 查看是否有工单同时关联了 test_order_id 和 order_id
|
||
|
|
SELECT
|
||
|
|
id,
|
||
|
|
name,
|
||
|
|
test_order_id AS '原始订单',
|
||
|
|
order_id AS '生成订单',
|
||
|
|
test_eut_id AS '样品ID',
|
||
|
|
create_time
|
||
|
|
FROM test_work_order
|
||
|
|
WHERE test_order_id IN (36, 37, 38, 39) OR order_id IN (36, 37, 38, 39)
|
||
|
|
ORDER BY create_time DESC;
|