再次请教：用SQL实现连接表格，并作出数据透析表

weizhuzhu

上次在坛子里请教，成功将TB,BU 和MAPPING三个表合并，形成了一个表格ACTUAL,
下面要进行第二步：将ACTUAL和另一个独立的表格BUDGET合并形成一个类似PV的数据透析表，而且成功了。
但进一步想加个限制条件，就是当某一行的数据都是0时，就不显示这一行，语句如下：
Select "Budget", BU,Currency, 0 as[Monthly Actual], 0 as [YTD Actual],sum([Monthly Budget]) as [Monthly Budget], sum([YTD Budget])as [YTD Budget], Category from [Budget$] union all Select "Actual", BU,Currency, sum([Monthly Actual]) as [Monthly Actual], sum([YTD Actual]) as [YTD Actual],0 as [Monthly Budget], 0 as[YTD Budget], Category from [Actual$] group by BU,Currency, Category having (sum([Monthly Budget])+sum([YTD Budget])+sum([Monthly Actual])+sum([YTD Actual]))<>0
系统提醒：试图包含不是聚合字段的BU。。。。。。
请大侠帮忙分析下。谢谢！
Report-test1.rar (275.89 KB, 下载次数: 28)

2010-8-19 21:00 上传

点击文件名下载附件

weizhuzhu

对不起，因附件超出范围，只好把PV数据透析表给删了。

suwenkai

不能聚合是你的第一个查询少了group by BU,Currency, Category
后面的条件好不是这么用的
having (sum([Monthly Budget])+sum([YTD Budget])+sum([Monthly Actual])+sum([YTD Actual]))<>0

capricorn

不看附件也知道问题出在哪啦，union all纵向合同两表，橙色语句部分只对Actual表进行了分组判断，而没有对Budget表进行分组判断

Select "Budget", BU,Currency, 0 as[Monthly Actual], 0 as [YTD Actual],sum([Monthly Budget]) as [Monthly Budget], sum([YTD Budget])as [YTD Budget], Category
from [Budget$]
group by BU,Currency, Category
having (sum([Monthly Budget])+sum([YTD Budget]))<>0
union all
Select "Actual", BU,Currency, sum([Monthly Actual]) as [Monthly Actual], sum([YTD Actual]) as [YTD Actual],0 as [Monthly Budget], 0 as[YTD Budget], Category
from [Actual$]
group by BU,Currency, Category
having (sum([Monthly Actual])+sum([YTD Actual]))<>0

weizhuzhu

理解了。谢谢各位！