SQL实现：查询各季度营收最高的公司及对应营收

表结构与样例数据 #

现有COMPANY_REVENUE表用于存储公司季度营收数据，包含4个字段：公司名称、所属季度、年份、对应季度营收，样例数据如下：

查询需求 #

忽略年份维度，查询每个季度营收最高的公司名称及对应营收值，以上述样例为例，期望返回结果如下：

原有SQL问题 #

之前编写的SQL无法返回正确结果，代码如下：

SELECT DISTINCT(C1.QUARTER), 
       C1.REVENUE 
FROM COMPANY_REVENUE C1, 
     COMPANY_REVENUE C2 
WHERE C1.REVENUE = GREATEST(C1.REVENUE, C2.REVENUE); 

核心问题有3点：

• 自连接时未通过Quarter字段做匹配限定，两张表生成全量笛卡尔积，对比的不是同季度下的营收数据
• 查询字段缺失Company_name，无法返回最高营收对应的公司名称
• DISTINCT仅能对返回结果做行级去重，无法实现分组取最大值对应完整记录的筛选逻辑

正确SQL写法 #

写法1：窗口函数（推荐，适配支持SQL:2003标准的主流数据库，如MySQL8+、PostgreSQL、SQL Server等） #

SELECT 
  Quarter AS QUARTER,
  Company_name AS COMPANY_NAME,
  Revenue AS REVENUE
FROM (
  SELECT 
    *,
    RANK() OVER(PARTITION BY Quarter ORDER BY Revenue DESC) AS rk
  FROM COMPANY_REVENUE
) t
WHERE rk = 1;

注：如果同一季度存在多家公司营收并列最高的场景，RANK()会返回所有并列最高的记录；如果要求每个季度仅返回1条记录，可将RANK()替换为ROW_NUMBER()。

写法2：关联子查询（兼容不支持窗口函数的老版本数据库） #

SELECT 
  c1.Quarter AS QUARTER,
  c1.Company_name AS COMPANY_NAME,
  c1.Revenue AS REVENUE
FROM COMPANY_REVENUE c1
WHERE (c1.Quarter, c1.Revenue) IN (
  SELECT Quarter, MAX(Revenue)
  FROM COMPANY_REVENUE
  GROUP BY Quarter
);

内容的提问来源于stack exchange，提问作者Stack_coder