SQL Server 2012中按con_num分组，获取最大date_entered对应的lead_id

对于SQL Server 2012，你可以使用ROW_NUMBER()窗口函数实现需求，该函数能为每个con_num分组内的记录按date_entered降序编号，取编号为1的记录即可得到每个分组中date_entered最大的那条数据。

修改后的查询语句 #

WITH RankedLeads AS (
    SELECT 
        leads.id AS lead_id,
        leads.date_entered,
        so.con_num,
        so.ord_date,
        -- 按con_num分组，组内按date_entered降序排序生成行号
        ROW_NUMBER() OVER (PARTITION BY so.con_num ORDER BY leads.date_entered DESC) AS rn
    FROM crm.leads 
        INNER JOIN crm.contacts ON leads.contact_id = contacts.id 
        INNER JOIN crm.email_addr_bean_rel rel ON rel.bean_id = contacts.id 
        INNER JOIN crm.email_addresses email ON email.id = rel.email_address_id 
        INNER JOIN sales_order so ON so.bill_email = email.email_address OR so.dt_email = email.email_address 
    WHERE rel.bean_module = 'contacts' AND so.ord_date >= leads.date_entered
)
SELECT lead_id, date_entered, con_num, ord_date
FROM RankedLeads
WHERE rn = 1; -- 保留每个分组中排名第一的记录

说明 #

• PARTITION BY so.con_num：将结果集按con_num分组，每个分组单独计算行号。
• ORDER BY leads.date_entered DESC：在每个分组内按date_entered从大到小排序，最大的date_entered对应行号为1。
• 外层筛选rn = 1的记录，即可得到每个con_num分组中date_entered最大的完整数据。

内容的提问来源于stack exchange，提问作者m-albert