In the case of multi-Table Association, the inline table results are generated by the external method and continue to be filtered.
Code
Select max (A. cityid) as cityid, sum (stockinfo. haverate) as haverate, provincename, count (stockholderid) as percount
From (
Select top 50 stockholderid, haverate, cityid, provincename from (
Select top 50 stockholder. stockholderid, haverate, stockholder. cityid,
IIF (A. provincename <> '', A. provincename, 'others') as provincename
From stockinfo, capitalstock,
(Select stockholder. stockholderid, provincename, stockholder. cityid from
(Stockholder left join areainfo on areainfo. cityid = stockholder. cityid)
) As
Where a. stockholderid = stockinfo. stockholderid
And capitalstock. capitalstockid = stockinfo. capitalstockid
And capitalstock. inputtime = #2010-05-28 # order by havenum DESC
)
) As a group by provincename