如何将获取Full Name的子查询嵌入主SQL查询以新增字段？

方法1：使用LEFT JOIN（推荐） #

将获取「Full Name」的查询作为子查询，与主表Single_Queues通过TransNo关联，既保证数据完整性（无评论的交易也能保留），也能提升查询效率：

select 
    sq.TransNo as 'Transaction No.',
    sq.Status as 'Status',
    sq.CliNam as 'Account Holder Name',
    COALESCE(sc.FullName, '') as 'Full Name', -- 处理无评论的场景，返回空字符串
    sq.PayType as 'Receipt Type',
    sq.Amount/100 AS 'Total',
    sq.InputDateTime as 'Amount Input date',
    sq.InputBy as 'Input by',
    sq.AuthorisedBy As 'Authorised By',
    sq.ReleasedBy As 'Released By'
from Single_Queues sq
left join (
    select 
        TransNo,
        STUFF((
            SELECT ' ' + CAST([Comment] AS VARCHAR(MAX)) 
            FROM Single_Queues_Comments I
            WHERE I.TransNo = O.TransNo and I.CommentNo = 1
            FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
          ,1,1,'') as FullName
    from Single_Queues_Comments O
    group by TransNo
) sc on sq.TransNo = sc.TransNo
order by sq.InputDateTime asc

方法2：使用相关子查询 #

如果需要直接在字段中嵌入子查询，需修改关联逻辑，确保子查询仅返回当前交易对应的单行结果：

select 
    TransNo as 'Transaction No.',
    Status as 'Status',
    CliNam as 'Account Holder Name',
    -- 子查询直接关联主表的TransNo，仅返回对应交易CommentNo=1的内容
    (
        SELECT STUFF(
            (SELECT ' ' + CAST([Comment] AS VARCHAR(MAX)) 
             FROM Single_Queues_Comments I
             WHERE I.TransNo = sq.TransNo and I.CommentNo = 1
             FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
          ,1,1,'')
    ) as 'Full Name',
    PayType as 'Receipt Type',
    Amount/100 AS 'Total',
    InputDateTime as 'Amount Input date',
    InputBy as 'Input by',
    AuthorisedBy As 'Authorised By',
    ReleasedBy As 'Released By'
from Single_Queues sq
order by InputDateTime asc

原写法的问题 #

1. 子查询中使用的O.TransNo仅关联了Single_Queues_Comments表，未与主表Single_Queues的TransNo建立关联，逻辑错误；
2. 字段内的子查询要求返回单行结果，原写法的子查询带group by TransNo但未关联主表，可能返回多条数据，触发报错。

内容的提问来源于stack exchange，提问作者Stuart Irvine