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
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
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
列 'b.ADProductID' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
(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