141 lines
2.6 KiB
Transact-SQL
141 lines
2.6 KiB
Transact-SQL
/* 修改人:Ld 修改时间:2026-08-06 15:22:59; 将测试库角色2下可明确归属的二级组件迁移到新一级菜单下 */
|
|
SET XACT_ABORT ON;
|
|
BEGIN TRAN;
|
|
|
|
DECLARE @MoveMap TABLE
|
|
(
|
|
id INT PRIMARY KEY,
|
|
目标pid INT NOT NULL,
|
|
顺序 INT NOT NULL
|
|
);
|
|
|
|
INSERT INTO @MoveMap (id, 目标pid, 顺序)
|
|
VALUES
|
|
(67, 160, 1),
|
|
(11, 160, 2),
|
|
(12, 160, 3),
|
|
(13, 160, 4),
|
|
(14, 160, 5),
|
|
(147, 160, 6),
|
|
(68, 160, 7),
|
|
(17, 160, 8),
|
|
(16, 160, 9),
|
|
(18, 160, 10),
|
|
(81, 160, 11),
|
|
(19, 160, 12),
|
|
(15, 160, 13),
|
|
(99, 160, 14),
|
|
(86, 160, 15),
|
|
(87, 160, 16),
|
|
(108, 160, 17),
|
|
(114, 160, 18),
|
|
(120, 160, 19),
|
|
(82, 160, 20),
|
|
(129, 160, 21),
|
|
(155, 160, 22),
|
|
(156, 160, 23),
|
|
(35, 160, 24),
|
|
(36, 160, 25),
|
|
(37, 160, 26),
|
|
(41, 160, 27),
|
|
(124, 160, 28),
|
|
(157, 160, 29),
|
|
(94, 161, 1),
|
|
(78, 161, 2),
|
|
(28, 161, 3),
|
|
(98, 161, 4),
|
|
(80, 161, 5),
|
|
(29, 161, 6),
|
|
(79, 161, 7),
|
|
(30, 161, 8),
|
|
(100, 161, 9),
|
|
(84, 161, 10),
|
|
(95, 161, 11),
|
|
(153, 161, 12),
|
|
(154, 161, 13),
|
|
(25, 162, 1),
|
|
(69, 162, 2),
|
|
(24, 162, 3),
|
|
(102, 162, 4),
|
|
(21, 162, 5),
|
|
(22, 162, 6),
|
|
(23, 162, 7),
|
|
(117, 162, 8),
|
|
(134, 162, 9),
|
|
(150, 162, 10),
|
|
(104, 163, 1),
|
|
(110, 163, 2),
|
|
(103, 163, 3),
|
|
(121, 163, 4),
|
|
(128, 163, 5),
|
|
(131, 163, 6),
|
|
(135, 163, 7),
|
|
(43, 164, 1),
|
|
(112, 164, 2),
|
|
(44, 164, 3),
|
|
(46, 164, 4),
|
|
(47, 164, 5),
|
|
(48, 164, 6),
|
|
(49, 164, 7),
|
|
(75, 164, 8),
|
|
(51, 164, 9),
|
|
(76, 164, 10),
|
|
(77, 164, 11),
|
|
(50, 164, 12),
|
|
(92, 164, 13),
|
|
(93, 164, 14),
|
|
(96, 164, 15),
|
|
(118, 164, 16),
|
|
(122, 164, 17),
|
|
(123, 164, 18),
|
|
(130, 164, 19),
|
|
(138, 164, 20),
|
|
(143, 164, 21),
|
|
(151, 164, 22),
|
|
(158, 164, 23),
|
|
(45, 164, 24),
|
|
(132, 159, 1);
|
|
|
|
DECLARE @PidOrder TABLE
|
|
(
|
|
pid INT PRIMARY KEY,
|
|
基础序号 INT NOT NULL
|
|
);
|
|
|
|
INSERT INTO @PidOrder (pid, 基础序号)
|
|
SELECT
|
|
m.目标pid,
|
|
ISNULL(MAX(r.角色模块顺序), 0) AS 基础序号
|
|
FROM @MoveMap m
|
|
LEFT JOIN dbo.基础表_角色模块信息 r
|
|
ON r.pid = m.目标pid
|
|
AND r.角色编号 = 2
|
|
AND ISNULL(r.是否启用, 1) = 1
|
|
AND NOT EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM @MoveMap mm
|
|
WHERE mm.id = r.id
|
|
)
|
|
GROUP BY m.目标pid;
|
|
|
|
UPDATE r
|
|
SET r.pid = m.目标pid,
|
|
r.模块顺序 = r.id,
|
|
r.是否启用 = 1,
|
|
r.角色模块顺序 = p.基础序号 + m.顺序
|
|
FROM dbo.基础表_角色模块信息 r
|
|
INNER JOIN @MoveMap m
|
|
ON m.id = r.id
|
|
INNER JOIN @PidOrder p
|
|
ON p.pid = m.目标pid
|
|
WHERE r.角色编号 = 2;
|
|
|
|
SELECT p.title AS 新一级菜单, m.id AS 二级ID, m.目标pid, m.顺序, r.角色模块顺序
|
|
FROM @MoveMap m
|
|
INNER JOIN dbo.基础表_模块信息 p ON p.id = m.目标pid
|
|
INNER JOIN dbo.基础表_角色模块信息 r ON r.id = m.id AND r.角色编号 = 2
|
|
ORDER BY m.目标pid, r.角色模块顺序, m.id;
|
|
|
|
COMMIT TRAN;
|