时间戳

首页 > 解决方案 > 在 SQL Server (SSMS) 中替换 LEFT OUTER JOIN

sql-server - 在 SQL Server (SSMS) 中替换 LEFT OUTER JOIN

问题描述

当我尝试在下面的 VIEW 中创建唯一索引时，出现以下错误，

消息 10113，级别 16，状态 1，行 431
无法在视图“V3CONv.ADVANCED.BillsToBeDeletedVIEW”上创建索引，因为它使用 LEFT、RIGHT 或 FULL OUTER 连接，并且在索引视图中不允许使用 OUTER 连接。请考虑改用 INNER 联接。

INNER JOIN 将产生与所需不同的数据集，我如何替换 Microsoft SQL Server 中的 LEFT OUTER JOIN ？

CREATE VIEW ADVANCED.BillsToBeDeletedVIEW
WITH SCHEMABINDING
AS
    SELECT DISTINCT 
        I_BILLNUMBER 
    FROM 
        ADVANCED.BIF951_C 
    LEFT OUTER JOIN 
        ADVANCED.BIF003ToBeDeleted ON BIF003ToBeDeleted.C_CUSTOMER = ADVANCED.BIF951_C.C_CUSTOMER 
                                   AND BIF003ToBeDeleted.C_ACCOUNT = ADVANCED.BIF951_C.C_ACCOUNT
    WHERE 
        BIF003ToBeDeleted.C_CUSTOMER IS NOT NULL
        OR D_BILLDATE < '2016-06-01'

标签： sql-serversubstitution

解决方案

您可以使用两个单独的索引视图，如下所示：

create schema ADVANCED
go

    create table ADVANCED.BIF951_C(id int, I_BILLNUMBER int, D_BILLDATE datetime, C_CUSTOMER INT, C_ACCOUNT INT)

    CREATE TABLE ADVANCED.BIF003ToBeDeleted(ID INT, C_CUSTOMER INT, C_ACCOUNT INT)

    GO

    CREATE OR ALTER VIEW ADVANCED.BillsToBeDeletedVIEW_1
    WITH SCHEMABINDING
    AS
        SELECT  
            I_BILLNUMBER 
        FROM 
            ADVANCED.BIF951_C 
        JOIN 
            ADVANCED.BIF003ToBeDeleted ON BIF003ToBeDeleted.C_CUSTOMER = ADVANCED.BIF951_C.C_CUSTOMER 
                                       AND BIF003ToBeDeleted.C_ACCOUNT = ADVANCED.BIF951_C.C_ACCOUNT
     GO

     CREATE UNIQUE CLUSTERED INDEX IX_BillsToBeDeletedVIEW_1 ON ADVANCED.BillsToBeDeletedVIEW_1(I_BILLNUMBER)
     GO


    CREATE OR ALTER VIEW ADVANCED.BillsToBeDeletedVIEW_2
    WITH SCHEMABINDING
    AS


        SELECT I_BILLNUMBER
        FROM  ADVANCED.BIF951_C 
        WHERE D_BILLDATE < CONVERT(DATETIME,'2016-06-01',120)

        GO


     CREATE UNIQUE CLUSTERED INDEX IX_BillsToBeDeletedVIEW_2 ON ADVANCED.BillsToBeDeletedVIEW_2(I_BILLNUMBER)
     GO

     CREATE OR ALTER VIEW ADVANCED.BillsToBeDeletedVIEW
     AS 

       SELECT I_BILLNUMBER
       FROM BillsToBeDeletedVIEW_1
       UNION
       SELECT I_BILLNUMBER
       FROM BillsToBeDeletedVIEW_2

推荐阅读