不能对包含聚合或子查询的表达式执行聚合函数 [急]

不能对包含聚合或子查询的表达式执行聚合函数 [急] [已结贴,结贴人:gongsun]

gongsun
看看天空的白云
等级:

发表于：2008-05-21 18:25:21 楼主

不用视图，只用sql语句。

我用 select min(b.GetProductID) as GetOADID from GetProducts b group by b.ADProductID

可以查出：
GetOADID
1
8

--------------------------

我现在想把这个 GetOADID 来 Count() 一下。

比如：@CountGetOADID=Count(GetOADID)

----------------------------------

关键是我想在下面用到这个Count(GetOADID)

SQL code


select 
a.WebSiteHostID,
(select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName,
Count(GetOADID) as ADQuantity   --在这里
--换句话就是我想：
--(select Count(min(b.GetProductID)) as GetOADID from GetProducts b group by b.ADProductID) as ADQuantity 
--但这是不可以的啊。 有什么方法解决啊？？？？不用视图。

from WebSiteHostOrders a

inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID

group by a.WebSiteHostID

问题点数：100 回复次数：8 修改删除举报引用回复

select a.WebSiteHostID, (select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName, (select Count(GetProductID) as GetOADID from GetProducts where ADProductID = b.ADProductID) as ADQuantity from WebSiteHostOrders a inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID group by a.WebSiteHostID

select a.WebSiteHostID, (select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName, (select count(*) from (select ADProductID,min(b.GetProductID) as GetOADID from GetProducts b group by b.ADProductID) d where d.ADProductID = a.ADProductID) as ADQuantity from WebSiteHostOrders a inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID group by a.WebSiteHostID

--try select a.WebSiteHostID, (select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName, (select Count(*) from GetProducts where ADProductID=b.ADProductID) as ADQuantity from WebSiteHostOrders a inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID group by a.WebSiteHostID

select a.WebSiteHostID, (select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName, count(distinct b.ADProductID) as ADQuantity from WebSiteHostOrders a inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID group by a.WebSiteHostID