：求和和左连接双值

Question

我正在尝试从使用Microsoft 2008的可配置软件中获取数据。

我试图得到总金额，并从一张发票中得到一个名为Numero de Obra (工作量)的字段，问题是需要将一些项目加在一起才能得到总数，当我得到额外的字段数时，我的数据就会被复制。

这是一个查询：

SELECT 
    GVA12.FECHA_EMIS AS [Fecha de emisión] ,
    GVA53.T_Comp AS [Tipo comprobante] ,
    GVA53.N_Comp AS [Nro. comprobante] ,
    GVA12.COD_VENDED AS [Cód. vendedor] ,
    CASE GVA12.COD_VENDED WHEN '**' THEN 'CONTADO' ELSE GVA23.NOMBRE_VEN END AS [Nombre Vendedor] ,
    GVA12.COD_CLIENT AS [Cód. cliente] ,
    CASE GVA12.COD_CLIENT WHEN '000000' THEN 'OCASIONAL' ELSE GVA14.RAZON_SOCI END AS [Razón social] ,
    GVA12.COTIZ AS [Cotización] ,
    GVA45.[DESC] as [Num_Obra]],
    SUM(CASE WHEN GVA12.T_Comp <> 'FAC' and GVA15.Tipo_Comp = 'C' then (-1) ELSE (1) END * GVA53.CANTIDAD) AS [Cantidad] ,
    SUM( CASE WHEN GVA12.T_Comp <> 'FAC' and GVA15.Tipo_Comp = 'C' then (-1) ELSE (1) END *      CASE GVA12.Cotiz WHEN 0 THEN 0 ELSE           CASE 'BIMONCTE'               WHEN 'BIMONCTE' THEN (CASE GVA12.MON_CTE WHEN 1 THEN GVA53.IMP_NETO_P  ELSE GVA53. IMP_NETO_P  * GVA12.COTIZ END)                WHEN 'BIORIGEN' THEN (CASE GVA12.MON_CTE WHEN 1 THEN GVA53.IMP_NETO_P  / GVA12.COTIZ ELSE GVA53.IMP_NETO_P  END)                WHEN 'BICOTIZ'  THEN(CASE GVA12.MON_CTE WHEN 1 THEN GVA53.IMP_NETO_P  / 1 ELSE GVA53.IMP_NETO_P  * GVA12.COTIZ  / 1 END)           END       END ) AS [Total] 
FROM 
    GVA12 (NOLOCK)  
INNER JOIN 
    GVA53 (NOLOCK) ON GVA53.T_COMP = GVA12.T_COMP AND GVA53.N_COMP = GVA12.N_COMP 
 INNER JOIN 
    GVA23 (NOLOCK) ON GVA12.COD_VENDED = GVA23.COD_VENDED
 LEFT JOIN 
    GVA14 (NOLOCK) ON GVA12.COD_CLIENT = GVA14.COD_CLIENT
LEFT JOIN 
    GVA15 ON GVA15.IDENT_COMP = GVA12.T_COMP
LEFT JOIN 
    GVA45 (NOLOCK) ON GVA53.COD_ARTICU = 'NºOBRA' AND
GVA45.N_COMP = GVA53.N_COMP AND GVA45.N_RENGLON = GVA53.N_RENGL_V 
WHERE 
    (GVA53.COD_ARTICU <> 'Art. Ajuste') AND (GVA53.COD_ARTICU <> '')
    AND (GVA53.RENGL_PADR = 0 OR GVA53.INSUMO_KIT_SEPARADO =1)
GROUP BY 
    GVA12.FECHA_EMIS , GVA53.T_Comp , GVA53.N_Comp , GVA12.COD_VENDED , CASE GVA12.COD_VENDED WHEN '**' THEN 'CONTADO' ELSE GVA23.NOMBRE_VEN END , GVA12.COD_CLIENT , CASE GVA12.COD_CLIENT WHEN '000000' THEN 'OCASIONAL' ELSE GVA14.RAZON_SOCI END , GVA12.COTIZ,GVA45.[DESC]

我目前的产出：

number_invoce     client          Number_Of_Work  Total         
FAC A000500000354 Hospital        NULL              90
FAC A000500000354 Hospital        346300            0.000000

我的预期产出：

number_invoce     client          Number_Of_Work    Total         
A000500000354     Hospital        346300            90

我有很多不同的表格，大部分都与问题无关，所以我只会解释那些重要的：

GVA53:它有发票上的所有行

例如，将是

   number_invoce COD_ARTICU      AMOUNT N_RENGL_V
   A000500000354 Wall            $50    1
   A000500000354 Roof            $40    2
   A000500000354 number_of_work  $0     3

GVA45:它有一篇文章的描述

   number_invoce DESC   N_RENGL
   A000500000354        1
   A000500000354        2
   A000500000354 346300 3

在简历中:我需要从发票中得到总数，我需要对项目进行汇总，我需要从工作的项目号中获得DESC (description)字段，将n_rengl(行号)从GV45关联到n_rengl_v of GV53，并且COD_ARTIC等于number_of_work。一切正常，但我不明白为什么我的数据会被复制。

非常感谢

Answer 1

好的最后一次尝试:P

正如注释中所建议的那样，我不确定noLock在这里的价值，但是由于它们没有引起您想要解决的问题，所以我现在把它们留在了这里。不过，我不允许这样做。

问题是组by on GVA45.DESC由于左联接和实际值导致两个值为null。因为您只需要这些值，所以您需要让系统返回最大值并从组中删除它。

这可能会成功..。

SELECT 
    GVA12.FECHA_EMIS AS [Fecha de emisión] ,
    GVA53.T_Comp AS [Tipo comprobante] ,
    GVA53.N_Comp AS [Nro. comprobante] ,
    GVA12.COD_VENDED AS [Cód. vendedor] ,
    CASE GVA12.COD_VENDED WHEN '**' THEN 'CONTADO' ELSE GVA23.NOMBRE_VEN END AS [Nombre Vendedor] ,
    GVA12.COD_CLIENT AS [Cód. cliente] ,
    CASE GVA12.COD_CLIENT WHEN '000000' THEN 'OCASIONAL' ELSE GVA14.RAZON_SOCI END AS [Razón social] ,
    GVA12.COTIZ AS [Cotización] ,
    max(GVA45.[DESC]) as [Num_Obra]],
    SUM(CASE WHEN GVA12.T_Comp <> 'FAC' and GVA15.Tipo_Comp = 'C' then (-1) ELSE (1) END * GVA53.CANTIDAD) AS [Cantidad] ,
    SUM( CASE WHEN GVA12.T_Comp <> 'FAC' and GVA15.Tipo_Comp = 'C' then (-1) ELSE (1) END *      CASE GVA12.Cotiz WHEN 0 THEN 0 ELSE           CASE 'BIMONCTE'               WHEN 'BIMONCTE' THEN (CASE GVA12.MON_CTE WHEN 1 THEN GVA53.IMP_NETO_P  ELSE GVA53. IMP_NETO_P  * GVA12.COTIZ END)                WHEN 'BIORIGEN' THEN (CASE GVA12.MON_CTE WHEN 1 THEN GVA53.IMP_NETO_P  / GVA12.COTIZ ELSE GVA53.IMP_NETO_P  END)                WHEN 'BICOTIZ'  THEN(CASE GVA12.MON_CTE WHEN 1 THEN GVA53.IMP_NETO_P  / 1 ELSE GVA53.IMP_NETO_P  * GVA12.COTIZ  / 1 END)           END       END ) AS [Total] 
FROM 
    GVA12 (NOLOCK)  
INNER JOIN 
    GVA53 (NOLOCK) ON GVA53.T_COMP = GVA12.T_COMP AND GVA53.N_COMP = GVA12.N_COMP 
 INNER JOIN 
    GVA23 (NOLOCK) ON GVA12.COD_VENDED = GVA23.COD_VENDED
 LEFT JOIN 
    GVA14 (NOLOCK) ON GVA12.COD_CLIENT = GVA14.COD_CLIENT
LEFT JOIN 
    GVA15 ON GVA15.IDENT_COMP = GVA12.T_COMP
LEFT JOIN 
    GVA45 (NOLOCK) ON GVA53.COD_ARTICU = 'NºOBRA' AND
GVA45.N_COMP = GVA53.N_COMP AND GVA45.N_RENGLON = GVA53.N_RENGL_V 
WHERE 
    (GVA53.COD_ARTICU <> 'Art. Ajuste') AND (GVA53.COD_ARTICU <> '')
    AND (GVA53.RENGL_PADR = 0 OR GVA53.INSUMO_KIT_SEPARADO =1)
GROUP BY 
    GVA12.FECHA_EMIS , GVA53.T_Comp , GVA53.N_Comp , GVA12.COD_VENDED , CASE GVA12.COD_VENDED WHEN '**' THEN 'CONTADO' ELSE GVA23.NOMBRE_VEN END , GVA12.COD_CLIENT , CASE GVA12.COD_CLIENT WHEN '000000' THEN 'OCASIONAL' ELSE GVA14.RAZON_SOCI END , GVA12.COTIZ