我正在使用Microsoft SQL Svr管理Studio 2008。我没有创建临时表的权限(公司限制创建或修改表的能力),或者我会用它来解决这个问题。
我已经成功地使用联合查询来组合三个select查询的结果。现在我正在尝试对联盟的结果进行汇总。
当我执行下面的查询时,我收到:
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
在前面的任何一个问题上,我都找不到解决方案。
发布于 2011-04-11 02:18:59
您需要为派生表设置别名,还必须将group by与having子句一起使用。
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
发布于 2011-04-11 02:19:43
SQL Server要求您为派生表/内联视图定义表别名:
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
发布于 2011-04-11 02:18:22
我没有创建临时表的权限(公司限制创建或修改表的能力),或者我会用它来解决这个问题。
尝试使用表变量而不是临时表:
declare @t table (id int primary key, col1 varchar(50))
insert @t (col1) values ('hello table variable')
select * from @t
表变量可以做临时表可以做的大多数事情。
就像Martin的答案(现已删除)所建议的,考虑给子查询一个别名,如下所示:
select ... list of columns ...
from (
... subquery ...
) as SubQueryAlias
group by
col1
在您的子查询中,having
可能应该是where
...
FROM HitsAaa
WHERE (AAAPeriod = N'2010-10')
...
https://stackoverflow.com/questions/5613728
复制相似问题