将结果求和到一个联合all中

Question

我在SQL中有以下查询。

SELECT cast(A.CodigoArticulo as varchar) CodArticulo
  , cast(ap.Codigo as varchar ) CodArtProveedor
  , dlp.Precio Publico
  , 0 Interior 
from Articulos a 
join ArticuloProveedores ap on ap.ArticuloId = a.Id 
join DetallesListaPrecios DLP on DLP.ArticuloId = A.Id 
join ListasPrecios lp on lp.Id = dlp.ListaPreciosId
where lp.Id in ('1') 
union all
SELECT cast(A.CodigoArticulo as varchar) CodArticulo
  , cast(ap.Codigo as varchar ) CodArtProveedor
  , 0 
  , dlp.Precio  
FROM Articulos a
join ArticuloProveedores ap on ap.ArticuloId = a.Id
join DetallesListaPrecios DLP on DLP.ArticuloId = A.Id 
join ListasPrecios lp on lp.Id = dlp.ListaPreciosId
where lp.Id in ('4')

我得到的结果如下：

CodArticulo CodArtProveedor Publico     Interior

44380       K-7             697         0 
44380       K-7             0           767
00003       IM2757          0           2030
00003       IM2757          1845        0
00006       MTRJ6           156         0
00006       MTRJ6           0           172
00010       BERJ6           156         0
00010       BERJ6           0           172

我需要SUM ()列PUBLICO和INTERIOR。我想要的输出如下所示。

CodArticulo CodArtProveedor Publico     Interior

44380       K-7             697         767
00003       IM2757          1845        2030
00006       MTRJ6           156         172
00010       BERJ6           156         172

有哪些建议的方法可以让我获得预期的输出？

Answer 1

我建议使用CTE

;WITH cte(CodArticulo,CodArtProveedor,Publico,Interior) AS
(SELECT cast(A.CodigoArticulo as varchar) CodArticulo
  , cast(ap.Codigo as varchar ) CodArtProveedor
  , dlp.Precio Publico
  , 0 Interior 
from Articulos a 
join ArticuloProveedores ap on ap.ArticuloId = a.Id 
join DetallesListaPrecios DLP on DLP.ArticuloId = A.Id 
join ListasPrecios lp on lp.Id = dlp.ListaPreciosId
where lp.Id in ('1') 
union all
SELECT cast(A.CodigoArticulo as varchar) CodArticulo
  , cast(ap.Codigo as varchar ) CodArtProveedor
  , 0 
  , dlp.Precio  
FROM Articulos a
join ArticuloProveedores ap on ap.ArticuloId = a.Id
join DetallesListaPrecios DLP on DLP.ArticuloId = A.Id 
join ListasPrecios lp on lp.Id = dlp.ListaPreciosId
where lp.Id in ('4'))
select CodArticulo,CodArtProveedor,SUM(Publico),Sum(Interior) from cte group by CodArticulo,CodArtProveedor

Answer 2

我建议将这些UNION结果输入到一个临时表##tempResults中，然后使用Group By和Sum

select CodArticulo, CodArtProveedor,SUM(Publico) ,SUM(Interior) from ##tempResults
group by CodArticulo,CodArtProveedor

Answer 3

我不完全熟悉您的数据模型，但是根据查询，在SUM中放置一个条件可能会让您一次完成查询。在下面的示例中，执行了单个select (无联合)，并在SUM中使用了CASE语句以获得所需的结果：

SELECT   cast(A.CodigoArticulo as varchar)  CodArticulo
        ,cast(ap.Codigo as varchar )        CodArtProveedor
        ,SUM(CASE WHEN '1' THEN dlp.Precio ELSE 0 END)                      Publico
        ,SUM(CASE WHEN '4' THEN dlp.Precio ELSE 0 END)                      Interior 
FROM    Articulos a 
            JOIN ArticuloProveedores ap     ON ap.ArticuloId    = a.Id 
            JOIN DetallesListaPrecios DLP   ON DLP.ArticuloId   = A.Id 
            JOIN ListasPrecios lp           ON lp.Id            = dlp.ListaPreciosId
WHERE lp.Id in ('1', '4') 
GROUP BY A.CodigoArticulo, ap.Codigo