Início / dba / Perguntas / 90171
Accepted
Nelson
Asked: 2015-01-27 04:15:23 +0800 CST

Agrupando UNION ALL

  • 772

Não importa como eu tente agrupar o conjunto de registros gerado por essa consulta, ele simplesmente não o fará. A consulta funciona ok o problema é tentar agrupar por A

SELECT     CountryCode + + ProductNumber + + StockType AS A, 
           Quantity AS B, 
           Description AS C, 
           Price AS D

FROM       dbo.vwSalesInvoiceWHandling
UNION ALL
SELECT     CountryCode + + ProductNumber + + StockType AS A,  
           Quantity AS B, 
           Description AS C, 
           Price AS D

FROM       dbo.vwSalesInvoiceWOHandling
sql-server union

1 respostas

  1. Best Answer

    Você precisa usar uma tabela derivada ou um CTE. Usando tabela derivada:

    SELECT 
    A,
    SUM(B) AS SumQuantity,
    MIN(D) AS MinPrice             -- etc
FROM
  ( SELECT     CountryCode + ProductNumber + StockType AS A, 
               Quantity AS B, 
               Description AS C, 
               Price AS D

    FROM       dbo.vwSalesInvoiceWHandling
    UNION ALL
    SELECT     CountryCode + ProductNumber + StockType AS A,  
               Quantity AS B, 
               Description AS C, 
               Price AS D

    FROM       dbo.vwSalesInvoiceWOHandling
  ) 
    AS x      -- alias for the derived table
GROUP BY
    A ;

    ou, alternativamente, agrupar por em ambas as tabelas e, em seguida, unir todos. Nesse caso, você precisará de outro grupo por em uma tabela derivada, mas provavelmente será mais eficiente, principalmente porque você pode alterar o agrupamento por uma coluna calculada ( CountryCode + ProductNumber + StockType)para se tornar GROUP BY CountryCode, ProductNumber, StockType:

    SELECT 
    CountryCode + ProductNumber + StockType AS A,
    SUM(B) AS SumQuantity,
    MIN(D) AS MinPrice             -- etc
FROM
  ( SELECT     CountryCode, ProductNumber, StockType, 
               SUM(Quantity) AS B, 
               MIN(Price) AS D

    FROM       dbo.vwSalesInvoiceWHandling
    GROUP BY   CountryCode, ProductNumber, StockType
    UNION ALL
    SELECT     CountryCode, ProductNumber, StockType,  
               SUM(Quantity) AS B, 
               MIN(Price) AS D

    FROM       dbo.vwSalesInvoiceWOHandling
    GROUP BY   CountryCode, ProductNumber, StockType
  ) 
    AS x      -- alias for the derived table
GROUP BY
    CountryCode, ProductNumber, StockType ;
    • 5

relate perguntas