AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.212.sql

SET NAMES utf8mb4;
SET @db = DATABASE();

-- 1. ScheduleResultOpMaster 增加 PeriodDetRecId 列（关联 PeriodSequenceDet.RecID，一对一）
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE ScheduleResultOpMaster ADD COLUMN PeriodDetRecId BIGINT NULL COMMENT ''关联PeriodSequenceDet.RecID（一对一）''',
    'SELECT ''ScheduleResultOpMaster.PeriodDetRecId already exists'' AS info'
) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'ScheduleResultOpMaster' AND COLUMN_NAME = 'PeriodDetRecId');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 2. 为 PeriodDetRecId 建索引（加速 JOIN）
SET @idx = 'idx_srom_perioddetrecid';
SET @tbl = 'ScheduleResultOpMaster';
SET @exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = @tbl AND index_name = @idx);
SET @sql = IF(@exists = 0, CONCAT('CREATE INDEX ', @idx, ' ON ', @tbl, '(PeriodDetRecId)'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;