问将12条SQL语句简化为2条语句
EN

Stack Overflow用户

提问于 2019-12-05 22:01:43

回答 1查看 71关注 0票数 0

我有一个SQL脚本，我必须简化，但我不知道如何…我是SQL的新手。

这些语句是：

IF NOT EXISTS(SELECT * FROM permissions.role WHERE name = 'lettersOfGuarantee')
BEGIN
    INSERT INTO [permissions].[role] (name, description, assignable, editable, created_by, created_date, last_modified_by, last_modified_date)
    VALUES ('lettersOfGuarantee','Εγγυητικές Επιστολές', 1, 1,'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP);
END
GO

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'chequesInCollaterals')
              AND customer_category = 1 AND user_category = 1)
BEGIN
    INSERT INTO [permissions].[role_category_rl] (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
        SELECT id, 1, 1, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
        FROM [permissions].[role] 
        WHERE name = 'chequesInCollaterals';
END

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'lettersOfGuarantee')
                        AND customer_category = 1 AND user_category = 1)
    BEGIN
        INSERT INTO [permissions].[role_category_rl] (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
            SELECT id, 1, 1, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
            FROM [permissions].[role] 
            WHERE name = 'lettersOfGuarantee';
END
GO

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'chequesInCollaterals')
AND customer_category = 2 AND user_category = 1)
    BEGIN
        INSERT INTO [permissions].[role_category_rl]
            (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
            SELECT id, 1, 2, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
            FROM [permissions].[role] WHERE name = 'chequesInCollaterals';
END

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'lettersOfGuarantee')
AND customer_category = 2 AND user_category = 1)
    BEGIN
        INSERT INTO [permissions].[role_category_rl]
            (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
            SELECT id, 1, 2, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
            FROM [permissions].[role] WHERE name = 'lettersOfGuarantee';
END
GO

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'chequesInCollaterals')
AND customer_category = 3 AND user_category = 1)
    BEGIN
        INSERT INTO [permissions].[role_category_rl]
            (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
            SELECT id, 1, 3, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
            FROM [permissions].[role] WHERE name = 'chequesInCollaterals';
END

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'lettersOfGuarantee')
AND customer_category = 3 AND user_category = 1)
    BEGIN
        INSERT INTO [permissions].[role_category_rl]
            (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
            SELECT id, 1, 3, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
            FROM [permissions].[role] WHERE name = 'lettersOfGuarantee';
END
GO

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'chequesInCollaterals')
AND customer_category = 3 AND user_category = 2)
    BEGIN
        INSERT INTO [permissions].[role_category_rl]
            (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
            SELECT id, 2, 3, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
            FROM [permissions].[role] WHERE name = 'chequesInCollaterals';
END

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'lettersOfGuarantee')
AND customer_category = 3 AND user_category = 2)
    BEGIN
        INSERT INTO [permissions].[role_category_rl]
            (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
            SELECT id, 2, 3, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
            FROM [permissions].[role] WHERE name = 'lettersOfGuarantee';
END
GO

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'chequesInCollaterals')
AND customer_category = 4 AND user_category = 2)
    BEGIN
        INSERT INTO [permissions].[role_category_rl]
            (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
            SELECT id, 2, 4, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
            FROM [permissions].[role] WHERE name = 'chequesInCollaterals';
END

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'lettersOfGuarantee')
AND customer_category = 4 AND user_category = 2)
    BEGIN
        INSERT INTO [permissions].[role_category_rl]
            (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
            SELECT id, 2, 4, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
            FROM [permissions].[role] WHERE name = 'lettersOfGuarantee';
END
GO

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'chequesInCollaterals')
AND customer_category = 5 AND user_category = 2)
    BEGIN
        INSERT INTO [permissions].[role_category_rl]
            (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
            SELECT id, 2, 5, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
            FROM [permissions].[role] WHERE name = 'chequesInCollaterals';
END

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'lettersOfGuarantee')
AND customer_category = 5 AND user_category = 2)
    BEGIN
        INSERT INTO [permissions].[role_category_rl]
            (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
            SELECT id, 2, 5, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
            FROM [permissions].[role] WHERE name = 'lettersOfGuarantee';
END
GO

因为只有user_category，customer_category值在改变，所以我们必须声明一个列表：

DECLARE @categories table (customer_cat_id BIGINT, user_cat_id BIGINT);
INSERT INTO @categories
  VALUES (1, 1),
         (1, 2),
         (1, 3),
         (2, 4),
         (2, 5),
         (2, 6);

我不知道如何动态修改这两个变量。

sql-server

tsql

回答 1

Stack Overflow用户

回答已采纳

发布于 2019-12-05 22:50:15

您可以使用在select中创建的表变量来投影user_category和customer_category：

INSERT INTO [permissions].[role_category_rl]
(role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
SELECT id, cat.user_cat_id, cat.customer_cat_id, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
FROM @categories cat, [permissions].[role] WHERE name = 'lettersOfGuarantee';

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/59196870

复制

相似问题

问将12条SQL语句简化为2条语句
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将12条SQL语句简化为2条语句EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将12条SQL语句简化为2条语句
EN