同一Locations表下，SQL查询无法正确显示起终点地址问题排查

问题根源 #

• JOIN条件用OR导致数据混乱：你用l.id = dist.OriginLocationId or l.id = dist.DestinationLocationId关联Locations表，会让每条里程记录返回两行结果——一行匹配起点地址，一行匹配终点地址，而且两次取出的l.Address列名完全重复，根本分不清哪个是起点、哪个是终点。
• 重复列名无法区分地址：查询结果里两个l.Address没有别名，数据库返回的列名重复，不仅可读性差，程序里也没法正确绑定对应地址。

修复后的SQL语句 #

需要两次关联Locations表，分别对应起点和终点，给地址列加别名区分：

select  dist.DistanceId,
        dist.OriginLocationId,
        origin.Address as OriginAddress,  -- 起点地址别名
        dist.DestinationLocationId,
        dest.Address as DestinationAddress,  -- 终点地址别名
        dist.DistanceInMiles        
from DistanceMatrixFromGoogleMapsApi as dist
join locations as origin  -- 关联起点地址表
on origin.id = dist.OriginLocationId
join locations as dest  -- 关联终点地址表
on dest.id = dist.DestinationLocationId
where OriginLocationId in (30506,53097,30362,30505,30377,30378,30594,30379,30514,30518,30517,55055,30360,30515,30516,30616,30512,30511,30510,30513,30381,49129)
  and DestinationLocationId in (53097,30362,30505,30377,30378,30594,30379,30514,30518,30517,55055,30360,30515,30516,30616,30512,30511,30510,30513,30381,49129,34690)

修复后查询结果里，OriginAddress对应起点地址，DestinationAddress对应终点地址，每条里程记录仅返回一行，数据完全符合预期。

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