-- 工单展示表 工单为车载终端检修类型补充设备类型的数据
BEGIN;
-- 609032009808957447
SELECT @maxid := max(id)+ 1 FROM `dst_work_order`.`tbl_work_order_show` ;
INSERT INTO `dst_work_order`.`tbl_work_order_show` ( `id`, `work_order_id`, `name`, `value`, `type`, `sort` )
SELECT
@maxid := @maxid + 1,
w.id AS work_order_id,
'设备类型',
'T-Box',
0,
7
FROM
(
SELECT
d.id
FROM
(
SELECT
a.id
FROM
(
SELECT
t.id
FROM
tbl_work_order t,
tbl_work_order_show b
WHERE
t.type = 4
AND t.id = b.work_order_id
) a
GROUP BY
a.id
) d
WHERE
d.id NOT IN (
SELECT
d.id
FROM
(
SELECT
a.id
FROM
(
SELECT
t.id
FROM
tbl_work_order t,
tbl_work_order_show b
WHERE
t.type = 4
AND t.id = b.work_order_id
AND b.NAME = '设备类型'
) a
GROUP BY
a.id
) d
)
) w,
( SELECT @maxid := 0 ) ind,
( SELECT @maxid := max(id)+ 1 FROM `dst_work_order`.`tbl_work_order_show` ) tmp;
COMMIT;
-- 验证数据脚本
select d.id from (SELECT
a.id
FROM
(SELECT
t.id
FROM
tbl_work_order t,
tbl_work_order_show b
WHERE
t.type = 4
AND t.id = b.work_order_id ) a
GROUP BY
a.id ) d where d.id not in(
select d.id from (SELECT
a.id
FROM
(SELECT
t.id
FROM
tbl_work_order t,
tbl_work_order_show b
WHERE
t.type = 4
AND t.id = b.work_order_id and b.name ='设备类型' ) a
GROUP BY
a.id ) d );
1、场景tbl_work_order 工单表,tbl_work_order_show 工单数据展示表 历史数据补充到工单数据展示表上
2、原来历史数据的工单展示表是没有设备类型数据,因增加了设备类型,历史数据如果没有工单类型的则需要增加该设备类型数据
3、把类型等于4的车载终端检修的展示表增加设备类型数据,
效果如图: