分组依据中的SQL Server CTE错误

Question

我有一个很慢的存储过程(并且丢失了新数据)，我需要让它更快，所以这次我使用了CTE (第一次使用)。

旧的是

Declare @Cod_Func as int;
set @Cod_Func = 10310  

BEGIN
    SELECT
        D.Cod_Regional,  
        D.Nom_Regional + ' (' + CAST(COUNT(A.ID_Chegada) as varchar) + ')' as Nom_Regional  
    FROM   
        APS_CHEGADA A (NOLOCK)  
    INNER JOIN   
        APS_AcessoFilial B (NOLOCK) ON A.Cod_Regional = B.Cod_Regional 
                                    AND A.Cod_Filial = B.Cod_Filial 
                                    AND B.flg_situacao = 1  
    INNER JOIN  
        COR_Filial C (NOLOCK) ON A.Cod_Regional = C.Cod_Regional 
                              AND A.Cod_Filial = C.Cod_Filial  
    INNER JOIN  
        COR_Regional D (NOLOCK) ON C.Cod_RegionalAtual = D.Cod_Regional  
    WHERE   
        A.ID_ChegadaStatus = 2 
        AND B.Cod_Func = @Cod_Func  
        AND A.FLG_SITUACAO = 1
    GROUP BY   
        D.Cod_Regional, D.Nom_Regional 
End;

新的版本是：

BEGIN  
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    WITH tblRegionais AS 
    (
        SELECT DISTINCT
            [R].COD_Regional,
            [F].COD_Regional AS [COD_RegionalReal],
            [R].Nom_Regional
        FROM
            COR_Regional [R] WITH(NOLOCK)
        INNER JOIN
            COR_FILIAL [F] WITH(NOLOCK) ON [R].COD_REGIONAL = [F].COD_RegionalAtual
        INNER JOIN
            APS_AcessoFilial [AF] WITH(NOLOCK) ON [F].COD_Regional = [AF].COD_Regional
                                               AND [F].COD_Filial = [AF].COD_Filial
        WHERE
            [F].FLG_SituacaoRegistro = 1
            AND [AF].FLG_Situacao = 1
            AND [AF].COD_Func = @COD_Func
    ),
    tblChegadas AS
    ( 
        SELECT
            [R].COD_Regional,
            COUNT([C].ID_Chegada) AS [QTD_Chegada]
        FROM
            tblRegionais [R]
        INNER JOIN
            APS_Chegada [C] WITH(NOLOCK) ON [R].COD_RegionalReal = [C].COD_Regional
        WHERE
            [C].ID_ChegadaStatus = 2
        GROUP BY
            [R].COD_Regional
    ),
    tblSaida AS 
    (
        SELECT 
            [R].COD_Regional,
            RTRIM([R].Nom_Regional) + ' (' + CAST([C].QTD_Chegada AS VARCHAR(30)) + ')' AS [NOM_Regional]
        FROM
            tblRegionais [R]
        INNER JOIN
            tblChegadas [C] ON [R].COD_Regional = [C].COD_Regional
    )
    SELECT 
        [S].COD_Regional,
        [S].NOM_Regional
    FROM
        tblSaida [S];       
END

问题是现在新的速度慢了四倍，结果不在像上一个一样的组中，但我在"tblChegada“中使用了group by。

我还将这两个图像与执行时间相加

​

​

​

​

Answer 1

我认为，tblChegadas返回3条记录，但tblRegionais返回3条以上的记录(DISTINCT包含COR_FILIAL.COD_Regional)。

在执行GROUP BY [R].COD_Regional之后，您在tblSaida中执行了INNER JOIN，因此tblSaida返回的记录多于3条，因为tblRegionais返回的记录多于3条。