Am绑定映射两个表以显示以下数据:
表1"主源表"
╔════╦══════════╦═══════════╦════════════╗
║ ID ║ SourceID ║ ExternaID ║ SourceName ║
╠════╬══════════╬═══════════╬════════════╣
║ 1 ║ NULL ║ NULL ║ AA ║
║ 2 ║ 4 ║ NULL ║ BB ║
║ 3 ║ 5 ║ 1 ║ CC ║
║ 4 ║ 5 ║ 3 ║ DD ║
╚════╩══════════╩═══════════╩════════════╝
表2"具有两个来源ID的注册客户信息"
╔════════╦══════════╦═══════════╗
║ custID ║ sourceID ║ ExternaID ║
╠════════╬══════════╬═══════════╣
║ 4 ║ 4 ║ NULL ║
║ 5 ║ 5 ║ 1 ║
║ 6 ║ 5 ║ 1 ║
║ 7 ║ 5 ║ 3 ║
╚════════╩══════════╩═══════════╝
以下是所需的输出结果:
╔═══════════════════════════════════════╦══════════╦═══════════════════╦════════════╗
║ Total No. of Customers Per SourceName ║ SourceID ║ ExternalChannelID ║ SourceName ║
╠═══════════════════════════════════════╬══════════╬═══════════════════╬════════════╣
║ 0 ║ NULL ║ NULL ║ AA ║
║ 2 ║ 5 ║ 1 ║ CC ║
║ 1 ║ 5 ║ 3 ║ DD ║
║ 1 ║ 4 ║ NULL ║ BB ║
╚═══════════════════════════════════════╩══════════╩═══════════════════╩════════════╝
因此,在这里,为了找到客户使用的SourceName,我需要根据SourceID和ExternalID这两列将其与主源表进行映射。示例:如果客户的SourceID=NULL AND ExternalID=NULL,那么它应该从主表中检查这两列,并为我获取SourceName。如何做到这一点?
如果您的服务器支持is not distinct from
select count(*), t1.SourceName from table1 t1
join table2 t2 on t1.SourceId is not distinct from t2.SourceId
and t1.ExternalId is not distinct from t2.ExternalId
group by t1.SourceName
或
select count(*), t1.SourceName from table1 t1
join table2 t2 on (t1.SourceId = t2.SourceId
or (t1.SourceId is null and t2.SourceId is null))
and (t1.ExternalId = t2.ExternalId
or (t1.ExternalId is null and t2.ExternalId is null))
group by t1.SourceName
使用相关子查询:
select *,
(select isnull(count(*), 0) from customertable c
where (c.sourceid = m.sourceid or (c.sourceid is null and m.sourceid is null)) and
(c.externalid = m.externalid or (c.externalid is null and m.externalid is null)) )
from mastertable m