zjrs-jeecgBoot/.docs/sql/字典数据/20260616_招聘会管理字典数据.sql

-- ============================================================
-- 招聘会管理 - 字典数据初始化与修正
-- ============================================================

-- ============================================================
-- 1. 修正旧的举办方式 (fair_hold_method) 字典项
-- 先删除由于中间脚本错误生成的重复记录（保留原始ID）
-- 然后删除旧的记录并重新插入，以确保信息正确且无重复。
-- ============================================================
-- 清理非原始 ID 的重复记录
DELETE FROM DICTIONARY_ITEM WHERE DictionaryCode = 'fair_hold_method' AND DictionaryItemID NOT IN ('178060100000501', '178060100000502');

-- 删除旧的字典项，然后保持原ID重新插入 "线上(1) / 线下(2)"
DELETE FROM DICTIONARY_ITEM WHERE DictionaryCode = 'fair_hold_method' AND DictionaryItemID IN ('178060100000501', '178060100000502');

-- 重新插入，保持 ID 不变以关联旧数据
INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT '178060100000502', 'fair_hold_method', '1', 1, '线上', 1, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryItemID = '178060100000502');

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT '178060100000501', 'fair_hold_method', '2', 2, '线下', 2, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryItemID = '178060100000501');

-- ============================================================
-- 2. 创建新字典
-- ============================================================
INSERT INTO DICTIONARY (DictionaryCode, Name, OrderNo, RecordStatus, DictType)
SELECT 'fair_type', '招聘会类型', 2, 1, 0 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY WHERE DictionaryCode = 'fair_type');

INSERT INTO DICTIONARY (DictionaryCode, Name, OrderNo, RecordStatus, DictType)
SELECT 'fair_subject', '招聘会主体', 3, 1, 0 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY WHERE DictionaryCode = 'fair_subject');

-- ============================================================
-- 3. 插入 fair_type 字典项
-- ============================================================
INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'fair_type', '1', 1, '校园招聘', 1, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'fair_type' AND Code = '1');

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'fair_type', '2', 2, '社会招聘', 2, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'fair_type' AND Code = '2');

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'fair_type', '3', 3, '专场招聘', 3, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'fair_type' AND Code = '3');

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'fair_type', '4', 4, '大型招聘', 4, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'fair_type' AND Code = '4');

-- ============================================================
-- 4. 插入 fair_subject 字典项
-- ============================================================
INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'fair_subject', '1', 1, '政府机关', 1, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'fair_subject' AND Code = '1');

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'fair_subject', '2', 2, '高校', 2, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'fair_subject' AND Code = '2');

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'fair_subject', '3', 3, '企业', 3, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'fair_subject' AND Code = '3');

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'fair_subject', '4', 4, '人力资源机构', 4, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'fair_subject' AND Code = '4');