如何在主查询中获取子查询字段最大值（适配SFMC查询构建器）

我需要编写一个查询，返回多个字段，但要求Opportunity表的stage和yearid字段对应各学校的最大yearid值。每个学校仅返回一行，且Opportunity表的所有值都取自该学校最大yearid的记录。

预期结果 #

遇到的问题 #

尝试过SELECT子查询、子连接、ORDER BY yearid DESC OFFSET 0 ROWS等方法，但始终返回所有结果，无法得到预期的单条学校记录，怀疑是查询结构有误。

表结构与测试数据 #

CREATE TABLE account
(accountid INT NOT NULL
, accschoolname varchar(20) NOT NULL
, PRIMARY KEY (accountid)
) ;

INSERT INTO account VALUES (1001, 'schoola') ;
INSERT INTO account VALUES (1002, 'schoolb' ) ;
INSERT INTO account VALUES (1003, 'schoolc' ) ;


CREATE TABLE opportunity 
(oppid INT NOT NULL
, yearid int NOT NULL
, stage varchar(20) NOT NULL
, accountid int NOT NULL
, oppschoolname varchar(20) NOT NULL
, PRIMARY KEY (oppid)
) ;

INSERT INTO opportunity VALUES (1, 10013, 'Sent', 1001, 'schoola2021') ;
INSERT INTO opportunity VALUES (2, 10014, 'Run', 1001, 'schoola2022') ;
INSERT INTO opportunity VALUES (3, 10015, 'Close', 1001, 'schoola2023');
INSERT INTO opportunity VALUES (4, 10013, 'Sent', 1002, 'schoolb2021') ;
INSERT INTO opportunity VALUES (5, 10014, 'Run', 1002, 'schoolb2022') ;
INSERT INTO opportunity VALUES (6, 10015, 'Close', 1002, 'schoolb2023');
INSERT INTO opportunity VALUES (7, 10013, 'Sent', 1003, 'schoolc2021') ;
INSERT INTO opportunity VALUES (8, 10014, 'Run', 1003, 'schoolc2022') ;
INSERT INTO opportunity VALUES (9, 10015, 'Close', 1003, 'schoolc2023');

CREATE TABLE contact
(contactid INT NOT NULL
, oppid INT NOT NULL
, firstname varchar(20)
, lastname varchar(20)
, email varchar(20)
, PRIMARY KEY (contactid)
) ;

-- 我的错误查询
select a.accschoolname, max(yearid) as yearid, o.stage
from account a
join opportunity o on o.accountid = a.accountid
group by a.accschoolname, o.stage

补充说明 #

此查询将用于Salesforce Marketing Cloud（SFMC）原生查询构建器，不确定SFMC是否支持CTE，因此CTE相关方案可能不适用。

解决方案 #

方案1：子查询筛选最大yearid后关联 #

该方法先通过子查询获取每个account对应的最大yearid，再关联原表获取对应stage值，无需CTE，兼容性较好：

SELECT 
    a.accschoolname AS schoolname,
    o.yearid,
    o.stage
FROM account a
JOIN opportunity o 
    ON o.accountid = a.accountid
WHERE (o.accountid, o.yearid) IN (
    SELECT accountid, MAX(yearid)
    FROM opportunity
    GROUP BY accountid
)

方案2：窗口函数筛选最新记录 #

SFMC通常支持窗口函数，该方法通过ROW_NUMBER()为每个学校的记录按yearid降序编号，取编号为1的记录（即最大yearid的那条）：

SELECT 
    schoolname,
    yearid,
    stage
FROM (
    SELECT 
        a.accschoolname AS schoolname,
        o.yearid,
        o.stage,
        ROW_NUMBER() OVER (PARTITION BY a.accountid ORDER BY o.yearid DESC) AS rn
    FROM account a
    JOIN opportunity o ON o.accountid = a.accountid
) t
WHERE rn = 1

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