select companyid,year,market,max(sum(totalprice)) from t_order
where companyid is not null group by companyid,year,market
但是我想取得每个地区每年最大的销售额的公司
把它作为子查询
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
查询到的结果也一样
怎么才能取得每个地区每年最大的销售额的公司？
if object_id('[t_order]') is not null drop table [t_order]
go
create table [t_order]([year] int,[market] varchar(4),[totalprice] int,[companyid] int)
insert [t_order]
select 1,'本地',6,6 union all
select 1,'本地',11,1 union all
select 1,'本地',16,2 union all
select 1,'本地',22,1 union all
select 1,'本地',26,4 union all
select 1,'本地',36,5 union all
select 2,'本地',5,2 union all
select 2,'本地',10,3 union all
select 2,'本地',15,1 union all
select 2,'本地',19,2 union all
select 2,'本地',20,3 union all
select 2,'本地',29,4 union all
select 2,'区域',9,1 union all
select 2,'区域',10,3 union all
select 2,'区域',15,4 union all
select 2,'区域',13,6 union all
select 2,'区域',14,5 union all
select 2,'区域',20,2
--------------开始查询--------------------------
;
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 AND a.sum=b.max
----------------结果----------------------------
