时间戳

首页 > 解决方案 > SQL查询在应该有数值的连接后返回空值

sql-server - SQL查询在应该有数值的连接后返回空值

问题描述

我正在将现有表中的一些数据插入到新表中。有一个名为的特定列DescNitro在新表中返回为 0 或 Null。我已经检查了特定情况，并且 Null 值正在替换旧表上的数值。知道为什么会这样吗？下面的代码：

DECLARE @DataAtual date = '2021-11-01'; 
DECLARE @UltMes date = DATEADD(MONTH, -1, @DataAtual);
DECLARE @PenUltMes date = DATEADD(MONTH, -2, @DataAtual);
DECLARE @AnoUltMes integer = YEAR(@UltMes);
DECLARE @MesUltMes integer = MONTH(@UltMes);
DECLARE @AnoPenUltMes integer = YEAR(@PenUltMes);
DECLARE @MesPenUltMes integer = MONTH(@PenUltMes);

SELECT
    m.datAno
    ,m.datMes
    ,m.Segmento
    ,m.nomDiretoria
    ,m.nomFilial
    ,m.Ov
    ,o.[Região] AS Filial_Pri
    ,m.[Cod_Rede] AS Cod_Rede
    ,m.Desc_Rede
    ,m.CodSegTatica AS cod_Canal_SAP
    ,m.Canal_Pricing AS cod_Canal_Pri
    ,m.Des_Canal_Pricing
    ,m.codMaterial
    ,m.Vol_Meta AS Vol_Meta_Ciex
    ,m.Fat_Meta AS Fat_Meta_Ciex
    ,g.[DescNitro]
INTO
    [pri].[tblMetaPrecoteste]
FROM
    [pri].[tblDadMetaCiex] m
LEFT JOIN
    dbo.tblCadMaterial mat ON mat.codMaterial = m.codMaterial
LEFT JOIN
    [pri].[tblDadOvFilial] o ON o.[Organização] = m.Ov
LEFT JOIN 
    [pri].[tblDadArvore] a ON a.Material = m.codMaterial
                           AND a.[Filial] = o.[Região]
                           AND a.[Canal de Vendas] = m.[Des_Canal_Pricing]
LEFT JOIN
    (SELECT
         t.[codMaterial], 
         t.[codOrganizacaoVenda], 
         t.[codSegTatica], 
         t.[codRede], 
         SUM(t.[DescNitro]) AS DescNitro
     FROM
         [MktIntelligence].[dbo].[tbl_F_PM_join_real_fifo_meta_com_gp] t
     WHERE
         (t.datmes = @MesUltMes AND t.datAno = @AnoUltMes) 
         OR (t.datmes =  @MesPenUltMes AND t.datAno =  @AnoPenUltMes)  
     GROUP BY
         t.[codMaterial], t.[codOrganizacaoVenda], t.[codSegTatica], t.[codRede]) g ON m.[codMaterial] = g.[codMaterial]
               AND m.[Ov] = g.[codOrganizacaoVenda]
               AND m.[codSegTatica] = g.[codSegTatica]
               AND m.[Cod_Rede]  = g.[codRede]

标签： sql-servertsqljoinnullnumeric

解决方案

因为那是一个left join因此如果没有匹配，它将显示为 NULL 否则使用isnull(columnname,0)，因此当它为 NULL 时它将显示 0

推荐阅读