134 lines
8.5 KiB
MySQL
134 lines
8.5 KiB
MySQL
|
-- =====================================================
|
|||
|
|
-- 技术建议探讨优化 SQL
|
|||
|
|
-- 作者: 周启威
|
|||
|
|
-- 日期: 2026-01-14
|
|||
|
|
-- 版本: v1.6.004
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
-- =====================================================
|
|||
|
|
-- 1. 设备列表状态字典（核心、正常、停用、报废）
|
|||
|
|
-- 说明：复用equipment_status字典，删除旧数据(关机/待机/运行中/故障)，新增新数据
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
-- 1.1 删除旧的设备状态字典数据（关机A、待机B、运行中C、故障D）
|
|||
|
|
DELETE FROM `sys_dict_data` WHERE `dict_type` = 'equipment_status';
|
|||
|
|
|
|||
|
|
-- 1.2 添加新的设备状态字典数据（核心、正常、停用、报废）
|
|||
|
|
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `remark`)
|
|||
|
|
VALUES (0, '核心', 'core', 'equipment_status', NULL, 'danger', 'N', '0', 'admin', NOW(), '核心设备');
|
|||
|
|
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `remark`)
|
|||
|
|
VALUES (1, '正常', 'normal', 'equipment_status', NULL, 'success', 'Y', '0', 'admin', NOW(), '正常使用设备');
|
|||
|
|
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `remark`)
|
|||
|
|
VALUES (2, '停用', 'disabled', 'equipment_status', NULL, 'warning', 'N', '0', 'admin', NOW(), '停用设备');
|
|||
|
|
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `remark`)
|
|||
|
|
VALUES (3, '报废', 'scrapped', 'equipment_status', NULL, 'info', 'N', '0', 'admin', NOW(), '报废设备');
|
|||
|
|
|
|||
|
|
-- =====================================================
|
|||
|
|
-- 2. 点检结果字典（正常/异常）
|
|||
|
|
-- 说明：点检记录专用，替代inspection_result中的正常/异常
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
-- 2.1 添加点检结果字典类型
|
|||
|
|
INSERT IGNORE INTO `sys_dict_type` (`dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `remark`)
|
|||
|
|
VALUES ('点检结果', 'spotcheck_result', '0', 'admin', NOW(), '点检结果：正常/异常');
|
|||
|
|
|
|||
|
|
-- 2.2 添加点检结果字典数据
|
|||
|
|
INSERT IGNORE INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `remark`)
|
|||
|
|
VALUES (0, '正常', 'normal', 'spotcheck_result', NULL, 'success', 'Y', '0', 'admin', NOW(), '点检正常');
|
|||
|
|
INSERT IGNORE INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `remark`)
|
|||
|
|
VALUES (1, '异常', 'abnormal', 'spotcheck_result', NULL, 'danger', 'N', '0', 'admin', NOW(), '点检异常');
|
|||
|
|
|
|||
|
|
-- =====================================================
|
|||
|
|
-- 3. 巡检结果字典（合格/不合格）
|
|||
|
|
-- 说明：巡检记录专用，替代inspection_result中的合格/不合格
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
-- 3.1 添加巡检结果字典类型
|
|||
|
|
INSERT IGNORE INTO `sys_dict_type` (`dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `remark`)
|
|||
|
|
VALUES ('巡检结果', 'patrol_result', '0', 'admin', NOW(), '巡检结果：合格/不合格');
|
|||
|
|
|
|||
|
|
-- 3.2 添加巡检结果字典数据
|
|||
|
|
INSERT IGNORE INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `remark`)
|
|||
|
|
VALUES (0, '合格', 'qualified', 'patrol_result', NULL, 'success', 'Y', '0', 'admin', NOW(), '巡检合格');
|
|||
|
|
INSERT IGNORE INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `remark`)
|
|||
|
|
VALUES (1, '不合格', 'unqualified', 'patrol_result', NULL, 'danger', 'N', '0', 'admin', NOW(), '巡检不合格');
|
|||
|
|
|
|||
|
|
-- =====================================================
|
|||
|
|
-- 4. 设备维修明细来源字段
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
-- 4.1 添加设备维修明细来源字典类型
|
|||
|
|
INSERT IGNORE INTO `sys_dict_type` (`dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `remark`)
|
|||
|
|
VALUES ('维修明细来源', 'repair_entry_source', '0', 'admin', NOW(), '设备维修明细来源：点检/巡检/手动添加');
|
|||
|
|
|
|||
|
|
-- 4.2 添加设备维修明细来源字典数据
|
|||
|
|
INSERT IGNORE INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `remark`)
|
|||
|
|
VALUES (0, '点检', 'spotcheck', 'repair_entry_source', NULL, 'success', 'N', '0', 'admin', NOW(), '来源于点检');
|
|||
|
|
INSERT IGNORE INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `remark`)
|
|||
|
|
VALUES (1, '巡检', 'inspection', 'repair_entry_source', NULL, 'primary', 'N', '0', 'admin', NOW(), '来源于巡检');
|
|||
|
|
INSERT IGNORE INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `remark`)
|
|||
|
|
VALUES (2, '手动添加', 'manual', 'repair_entry_source', NULL, 'info', 'Y', '0', 'admin', NOW(), '手动添加');
|
|||
|
|
|
|||
|
|
-- 4.3 设备维修明细表添加来源字段
|
|||
|
|
SET @dbname = DATABASE();
|
|||
|
|
SET @tablename = 'dm_repair_order_entry';
|
|||
|
|
|
|||
|
|
-- 添加 source_type 字段
|
|||
|
|
SET @columnname = 'source_type';
|
|||
|
|
SET @preparedStatement = (SELECT IF(
|
|||
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
|||
|
|
'SELECT 1',
|
|||
|
|
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(32) DEFAULT ''manual'' COMMENT ''来源类型：spotcheck-点检, inspection-巡检, manual-手动添加''')
|
|||
|
|
));
|
|||
|
|
PREPARE alterIfNotExists FROM @preparedStatement;
|
|||
|
|
EXECUTE alterIfNotExists;
|
|||
|
|
DEALLOCATE PREPARE alterIfNotExists;
|
|||
|
|
|
|||
|
|
-- 添加 source_record_id 字段
|
|||
|
|
SET @columnname = 'source_record_id';
|
|||
|
|
SET @preparedStatement = (SELECT IF(
|
|||
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
|||
|
|
'SELECT 1',
|
|||
|
|
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' BIGINT DEFAULT NULL COMMENT ''来源记录ID（点检/巡检记录ID）''')
|
|||
|
|
));
|
|||
|
|
PREPARE alterIfNotExists FROM @preparedStatement;
|
|||
|
|
EXECUTE alterIfNotExists;
|
|||
|
|
DEALLOCATE PREPARE alterIfNotExists;
|
|||
|
|
|
|||
|
|
-- =====================================================
|
|||
|
|
-- 回滚脚本（如需回滚请执行以下语句）
|
|||
|
|
-- =====================================================
|
|||
|
|
/*
|
|||
|
|
-- 删除设备列表状态字典
|
|||
|
|
DELETE FROM `sys_dict_data` WHERE `dict_type` = 'equipment_list_status';
|
|||
|
|
DELETE FROM `sys_dict_type` WHERE `dict_type` = 'equipment_list_status';
|
|||
|
|
|
|||
|
|
-- 删除点检结果字典
|
|||
|
|
DELETE FROM `sys_dict_data` WHERE `dict_type` = 'spotcheck_result';
|
|||
|
|
DELETE FROM `sys_dict_type` WHERE `dict_type` = 'spotcheck_result';
|
|||
|
|
|
|||
|
|
-- 删除巡检结果字典
|
|||
|
|
DELETE FROM `sys_dict_data` WHERE `dict_type` = 'patrol_result';
|
|||
|
|
DELETE FROM `sys_dict_type` WHERE `dict_type` = 'patrol_result';
|
|||
|
|
|
|||
|
|
-- 删除维修明细来源字典
|
|||
|
|
DELETE FROM `sys_dict_data` WHERE `dict_type` = 'repair_entry_source';
|
|||
|
|
DELETE FROM `sys_dict_type` WHERE `dict_type` = 'repair_entry_source';
|
|||
|
|
|
|||
|
|
-- 删除维修明细表来源字段
|
|||
|
|
ALTER TABLE `dm_repair_order_entry` DROP COLUMN `source_type`;
|
|||
|
|
ALTER TABLE `dm_repair_order_entry` DROP COLUMN `source_record_id`;
|
|||
|
|
*/
|
|||
|
|
|
|||
|
|
|
|||
|
|
-- =====================================================
|
|||
|
|
-- 5. 点检/巡检/保养频率字典标签更新
|
|||
|
|
-- 说明：更新inspection_plan_frequency字典的label，添加具体执行时间
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
UPDATE `sys_dict_data` SET `dict_label` = '每日9点' WHERE `dict_type` = 'inspection_plan_frequency' AND `dict_value` = 'daily';
|
|||
|
|
UPDATE `sys_dict_data` SET `dict_label` = '每周一14点' WHERE `dict_type` = 'inspection_plan_frequency' AND `dict_value` = 'weekly';
|
|||
|
|
UPDATE `sys_dict_data` SET `dict_label` = '每月第一周周一14点' WHERE `dict_type` = 'inspection_plan_frequency' AND `dict_value` = 'monthly';
|
|||
|
|
UPDATE `sys_dict_data` SET `dict_label` = '每季度第一周周一14点' WHERE `dict_type` = 'inspection_plan_frequency' AND `dict_value` = 'quarterly';
|
|||
|
|
UPDATE `sys_dict_data` SET `dict_label` = '每年第一周周一14点' WHERE `dict_type` = 'inspection_plan_frequency' AND `dict_value` = 'yearly';
|