sql - 对联合查询求和

Question

我正在使用 Microsoft SQL Svr Mgmt Studio 2008。我无权创建临时表(公司限制创建或修改表的能力)，否则我将使用它来解决此问题。

我已成功使用联合查询来组合三个选择查询的结果。现在我正在尝试总结联盟的结果。

当我执行下面的查询时，我收到:

Incorrect syntax near the keyword 'GROUP'

然后当我删除该组时，我得到:

Incorrect syntax near ')'

这是我到目前为止的查询:

Select Period, PCC, SUM(BasicHits), SUM(FareHits), SUM(SearchHits)
From (

SELECT     AAAPeriod AS Period, 
AAAFromPCC AS PCC, 
- SUM(AAABasic) AS BasicHits,
- SUM(AAAFare) AS FareHits,
- SUM(AAASearch) AS SearchHits
FROM        HitsAaa
HAVING     (AAAPeriod = N'2010-10') 

UNION ALL

SELECT     AAAPeriod, 
AAAtoPCC,
SUM(AAABasic),
SUM(AAAFare),
SUM(AAASearch) 
FROM        HitsAaa
HAVING     (AAAPeriod = N'2010-10')

UNION ALL

SELECT      AgtPeriod, 
AgtPcc, 
SUM(AgtBasic),
SUM(AgtFare),
SUM(AgtSearch)
FROM          HitsAgent
HAVING      (AgtPeriod = N'2010-10')

)GROUP BY Period, PCC

对于之前的任何问题，我都无法找到解决方案。

Answer 1

您需要为派生表指定别名，还必须使用带有having 子句的group by。

SELECT
  q1.Period, 
  q1.PCC, 
  SUM(q1.BasicHits), 
  SUM(q1.FareHits), 
  SUM(q1.SearchHits)
FROM (SELECT     
        AAAPeriod AS Period, 
        AAAFromPCC AS PCC, 
        - SUM(AAABasic) AS BasicHits,
        - SUM(AAAFare) AS FareHits,
        - SUM(AAASearch) AS SearchHits
      FROM HitsAaa
      GROUP BY 
        AAAPeriod, 
        AAAFromPCC
      HAVING (AAAPeriod = N'2010-10') 

      UNION ALL

      SELECT     
        AAAPeriod AS Period, 
        AAAtoPCC AS PCC,
        SUM(AAABasic) AS BasicHits,
        SUM(AAAFare) AS FareHits,
        SUM(AAASearch) AS SearchHits 
      FROM HitsAaa
      GROUP BY 
        AAAPeriod, 
        AAAtoPCC
      HAVING (AAAPeriod = N'2010-10')

      UNION ALL

      SELECT
        AgtPeriod AS Period, 
        AgtPcc AS PCC, 
        SUM(AgtBasic) AS BasicHits,
        SUM(AgtFare) AS FareHits,
        SUM(AgtSearch)  AS SearchHits
      FROM HitsAgent
      GROUP BY 
        AgtPeriod, 
        AgtPCC
      HAVING (AgtPeriod = N'2010-10')) q1 
GROUP BY 
  q1.Period, 
  q1.PCC