当客户第一次在任何系统中注册时，都要花时间

Question

我在DB:中有一些表

C_Master_USER-共享用户(ID_USER(和其MasterID(ID_Master(之间的关系-来自不同系统但使用相同电子邮件的用户将具有相同的MasterID

V_DL_C_USER-包含每个用户的ID_Source和ID_SYS_TYPE

C_Source-包含源系统的原始名称

C_SYS_TYPE-包含系统类型的实体名称

我的简单任务是获取每个MasterID、它们的RegistrationDate和任何系统中首次注册的来源

SELECT top 50 
MasterID, RegistrationDate, SourceName, SystemName
FROM (
SELECT
a.ID_MASTER as MasterID, 
a.DATE_N as RegistrationDate,
c.Name2 as SourceName,
d.Name_2 as SystemName,
ROW_NUMBER() OVER(ORDER BY a.DATE_N ASC) as RN
FROM C_MASTER_USE a 
join V_DL_C_USER b on a.ID_USER = b.ID 
join c_source c on b.ID_Source = c.ID
join C_SYS_TYPE d on b.ID_SYS_TYPE = d.ID
) t
where RN = 1

但是，查询结果只有1，是所有系统中最新的用户。

MasterID    RegistrationDate        SourceName       SystemName
8825938     2020-11-10 08:17:30     Mobile App       Club

Answer 1

如果每个MasterId需要一行，请使用PARTITION BY:

SELECT top 50 
MasterID, RegistrationDate, SourceName, SystemName
FROM (
SELECT
a.ID_MASTER as MasterID, 
a.DATE_N as RegistrationDate,
c.Name2 as SourceName,
d.Name_2 as SystemName,
ROW_NUMBER() OVER(PARTITION BY a.ID_MASTER ORDER BY a.DATE_N ASC) as RN
FROM C_MASTER_USE a 
join V_DL_C_USER b on a.ID_USER = b.ID 
join c_source c on b.ID_Source = c.ID
join C_SYS_TYPE d on b.ID_SYS_TYPE = d.ID
) t
where RN = 1