首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >问答首页 >对联合查询求和

对联合查询求和
EN

Stack Overflow用户
提问于 2011-04-11 02:11:30
回答 5查看 23.5K关注 0票数 5

我正在使用Microsoft SQL Svr管理Studio 2008。我没有创建临时表的权限(公司限制创建或修改表的能力),或者我会用它来解决这个问题。

我已经成功地使用联合查询来组合三个select查询的结果。现在我正在尝试对联盟的结果进行汇总。

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

代码语言:javascript
运行
复制
Incorrect syntax near the keyword 'GROUP'

然后,当我通过以下方式删除组时:

代码语言:javascript
运行
复制
Incorrect syntax near ')'

到目前为止,我的查询如下:

代码语言:javascript
运行
复制
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

在前面的任何一个问题上,我都找不到解决方案。

EN

回答 5

Stack Overflow用户

回答已采纳

发布于 2011-04-11 02:18:59

您需要为派生表设置别名,还必须将group by与having子句一起使用。

代码语言:javascript
运行
复制
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
票数 6
EN

Stack Overflow用户

发布于 2011-04-11 02:19:43

SQL Server要求您为派生表/内联视图定义表别名:

代码语言:javascript
运行
复制
SELECT x.period, x.pcc, SUM(x.BasicHits), SUM(x.FareHits), SUM(x.SearchHits)
  FROM (SELECT AAAPeriod AS Period, 
               AAAFromPCC AS PCC, 
               - SUM(AAABasic) AS BasicHits,
               - SUM(AAAFare) AS FareHits,
               - SUM(AAASearch) AS SearchHits
          FROM HitsAaa
         WHERE AAAPeriod = N'2010-10'
      GROUP BY aaaperiod, aaafrompcc
        UNION ALL
        SELECT AAAPeriod, 
               AAAtoPCC,
               SUM(AAABasic),
               SUM(AAAFare),
               SUM(AAASearch) 
          FROM HitsAaa
         WHERE AAAPeriod = N'2010-10'
      GROUP BY aaaperiod, aaafrompcc
        UNION ALL
        SELECT AgtPeriod, 
               AgtPcc, 
               SUM(AgtBasic),
               SUM(AgtFare),
               SUM(AgtSearch)
          FROM HitsAgent
         WHERE AgtPeriod = N'2010-10'
      GROUP BY agtperiod, agtpcc) AS x
GROUP BY x.period, x.pcc
票数 5
EN

Stack Overflow用户

发布于 2011-04-11 02:18:22

我没有创建临时表的权限(公司限制创建或修改表的能力),或者我会用它来解决这个问题。

尝试使用表变量而不是临时表:

代码语言:javascript
运行
复制
declare @t table (id int primary key, col1 varchar(50))
insert @t (col1) values ('hello table variable')
select * from @t

表变量可以做临时表可以做的大多数事情。

就像Martin的答案(现已删除)所建议的,考虑给子查询一个别名,如下所示:

代码语言:javascript
运行
复制
select  ... list of columns ...
from    (
        ... subquery ...
        ) as SubQueryAlias
group by
        col1

在您的子查询中,having可能应该是where

代码语言:javascript
运行
复制
...
FROM        HitsAaa
WHERE       (AAAPeriod = N'2010-10') 
...
票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/5613728

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档