SQL多表关联查询：如何获取潜在客户首次意向登记的Event字段

要解决这个问题，核心是精准定位每个客户（Email）+地点分组下的最早登记记录，然后提取该记录的Event类型。推荐使用ROW_NUMBER()窗口函数实现，逻辑清晰且效率较高。

完整SQL代码 #

WITH RankedProspectEvents AS (
    SELECT
        p.Email,
        ph.[Location] AS [Contract],
        ph.[Event] AS [First Event Type],
        ph.[Date] AS [Event Date],
        -- 按Email+Location分组，按登记日期升序排名，最早的记录排第1
        ROW_NUMBER() OVER (PARTITION BY p.Email, ph.[Location] ORDER BY ph.[Date] ASC) AS EventRank
    FROM tblProspectHistory ph
    INNER JOIN tblProspectPerson pp 
        ON pp.ProspectID = ph.ProspectID 
        AND pp.MainContact = 1 -- 仅取主联系人
    INNER JOIN tblPerson p 
        ON p.PersonID = pp.PersonID
    WHERE 
        ph.[Date] >= '2021-07-01' -- 筛选近两年数据
        AND ph.[Event] IN ('WEBENQ','TELENQ','VISIT') -- 仅意向登记事件
        AND CHARINDEX('@', p.Email) > 0 -- 过滤无效邮箱
)
SELECT
    [Contract],
    [First Event Type],
    1 AS [Event Count], -- 固定值1
    [Event Date],
    Email
FROM RankedProspectEvents
WHERE EventRank = 1 -- 只保留每个分组的首次登记记录
ORDER BY Email, [Contract]

逻辑说明 #

1. CTE子查询RankedProspectEvents：

   • 先关联三张表，过滤出符合业务要求的记录；
   • 用ROW_NUMBER()窗口函数，以Email和Location为分组依据（PARTITION BY），按登记日期升序排序（ORDER BY ph.[Date] ASC），给每条记录分配排名；
   • 每个分组里最早的记录排名为1，后续记录依次递增。

2. 主查询：

   • 从CTE中筛选出排名为1的记录，就是每个客户-地点组合的首次登记记录；
   • 直接提取该记录的Event字段，就能得到首次登记对应的事件类型，同时保留你需要的其他字段。

为什么之前的方法失效？ #

• 直接加Event到GROUP BY：会把同一客户-地点下不同事件类型的记录拆分成多个分组，不符合“按客户+地点分组”的需求；
• 用MIN/MAX(Event)：只是对事件类型字符串做聚合，和首次登记日期没有关联，结果必然错误；
• 自连接失败：大概率是关联条件没覆盖到Email+Location+首次日期的组合，窗口函数的方式更直观避免这类问题。

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