select companyid,year,market,max(sum(totalprice)) from t_order
where companyid is not null group by companyid,year,market
如果不加max的话,查到的是
但是我想取得每个地区每年最大的销售额的公司
把它作为子查询
select companyid,market,year,max(sum) as max from
( select companyid,year,market,sum(totalprice) as sum from t_order
where companyid is not null group by companyid,year,market ) a
group by companyid, year,market
查询到的结果也一样
怎么才能取得每个地区每年最大的销售额的公司？
------解决方案--------------------
试一下:
;
WITH cte
AS ( SELECT companyid ,
year ,
market ,
SUM(totalprice) [sum]
FROM t_order
WHERE companyid IS NOT NULL
GROUP BY companyid ,
year ,
market
)
SELECT a.companyid ,
a.[year] ,
a.market ,
b.[max]
FROM cte a
INNER JOIN ( SELECT [year] ,
market ,
MAX([sum]) [max]
FROM cte
GROUP BY [year] ,
market
) b ON a.[year] = b.[year]
AND a.market = b.market
------解决方案--------------------
...
) b ON a.[year] = b.[year]
AND a.market = b.market
AND a.[sum] = b.[max] -- 1楼少了这个条件