SQL Server内连接查询结果重复，如何获取关联表第一条匹配记录？

核心逻辑是为[eSUPPORT].[HS_SP_ENGINEER_ALLOCATION]表中同一ISU_CODE下的多条记录添加排序序号，仅保留第一条匹配记录再和主表关联，避免1:N关联导致的结果重复。

方案1：使用ROW_NUMBER()窗口函数过滤 #

修改后的完整SQL如下：

select 
    '' as 'Project Code', 
    client.CLI_NAME as 'Plant', 
    issue.ISU_CODE as 'Log ID' ,
    issue.ISU_DESCRIPTION  as 'Description',
    issue.ISS_CODE as 'Status',
    issue.MOD_CODE as 'Module Name',
    issue.ISU_COMMENT as 'Comment',
    issue.USR_CODE_LOG as 'Loged User',
    issue.ISU_REPORT_DATE as 'Reported Date',
    issue.ISC_CODE as 'Issue Type',
    issue.ISU_CLOSED_DATE as 'Solve Date',
    severiry.SEV_NAME as 'Serverity Level',
    '' as 'Follow up Comment',
    CONCAT(eng_allow.LOG_DATE,'-',sys_user.USR_NAME,'-',eng_allow.COMMENT) as 'Engineer Allocation Comment'
from 
    [eSUPPORT].[HS_SP_ISSUE] as issue
inner join 
    [eSUPPORT].[HS_SP_CLIENT_SITE] as cli_site
on
    issue.CLS_CODE=cli_site.CLS_CODE
inner join
    [eSUPPORT].[HS_SP_CLIENT] as client
on
    cli_site.CLI_CODE=client.CLI_CODE
-- 修改工程师分配表关联逻辑：添加行号过滤，仅取每个ISU_CODE的第一条记录
inner join (
    SELECT 
        *,
        -- 按ISU_CODE分组，按LOG_DATE升序排序，你可以根据需求调整排序字段和顺序
        ROW_NUMBER() OVER(PARTITION BY ISU_CODE ORDER BY LOG_DATE ASC) AS rn
    FROM [eSUPPORT].[HS_SP_ENGINEER_ALLOCATION]
) as eng_allow
on
    issue.ISU_CODE=eng_allow.ISU_CODE 
    AND eng_allow.rn = 1 -- 仅保留第一条匹配记录
inner join
    [eSUPPORT].[HS_SP_SYSTEM_USER] as sys_user
on
    eng_allow.USR_CODE=sys_user.USR_CODE
inner join
    [eSUPPORT].[HS_SP_SEVERITY] as severiry
on
    issue.SEV_CODE=severiry.SEV_CODE
where
    issue.ISU_CODE='060307'

说明 #

• 排序规则可自行调整：如果要取最新的分配记录，将ORDER BY LOG_DATE ASC修改为ORDER BY LOG_DATE DESC即可，也可以替换为你需要的其他排序字段（比如表主键ID）。
• 其余字段、关联逻辑和原有语句完全一致，不会影响原有查询结果的准确性。

方案2：使用CROSS APPLY获取单条记录 #

如果你只需要拼接后的Engineer Allocation Comment字段，也可以用CROSS APPLY写法，性能更优：

select 
    '' as 'Project Code', 
    client.CLI_NAME as 'Plant', 
    issue.ISU_CODE as 'Log ID' ,
    issue.ISU_DESCRIPTION  as 'Description',
    issue.ISS_CODE as 'Status',
    issue.MOD_CODE as 'Module Name',
    issue.ISU_COMMENT as 'Comment',
    issue.USR_CODE_LOG as 'Loged User',
    issue.ISU_REPORT_DATE as 'Reported Date',
    issue.ISC_CODE as 'Issue Type',
    issue.ISU_CLOSED_DATE as 'Solve Date',
    severiry.SEV_NAME as 'Serverity Level',
    '' as 'Follow up Comment',
    alloc.EngineerComment as 'Engineer Allocation Comment'
from 
    [eSUPPORT].[HS_SP_ISSUE] as issue
inner join 
    [eSUPPORT].[HS_SP_CLIENT_SITE] as cli_site
on
    issue.CLS_CODE=cli_site.CLS_CODE
inner join
    [eSUPPORT].[HS_SP_CLIENT] as client
on
    cli_site.CLI_CODE=client.CLI_CODE
-- 直接取每个ISU_CODE对应的第一条分配记录
CROSS APPLY (
    SELECT TOP 1 CONCAT(ea.LOG_DATE,'-',su.USR_NAME,'-',ea.COMMENT) AS EngineerComment
    FROM [eSUPPORT].[HS_SP_ENGINEER_ALLOCATION] ea
    INNER JOIN [eSUPPORT].[HS_SP_SYSTEM_USER] su ON ea.USR_CODE = su.USR_CODE
    WHERE ea.ISU_CODE = issue.ISU_CODE
    ORDER BY ea.LOG_DATE ASC
) AS alloc
inner join
    [eSUPPORT].[HS_SP_SEVERITY] as severiry
on
    issue.SEV_CODE=severiry.SEV_CODE
where
    issue.ISU_CODE='060307'

内容的提问来源于stack exchange，提问作者Asanka Yaparathna