如何在Sales Invoice Lines表中基于同表同No_的非0单价交易生成新列？

关联子查询方案 #

直接通过关联子查询匹配同No_下单价为0的行，获取对应的Quantity值。如果同一个No_下有多行单价0的记录，记得加TOP 1或者聚合函数避免报错：

SELECT 
    [No_], 
    [Document No_], 
    Quantity, 
    [Line Discount _], 
    [Unit Price],
    new_column = (
        SELECT TOP 1 Quantity 
        FROM [Sales Invoice Lines] si2 
        WHERE si2.[No_] = si.[No_] 
          AND CONVERT(decimal, si2.[Unit Price]) = 0
    )
FROM [Sales Invoice Lines] si
WHERE CONVERT(decimal, si.[Unit Price]) != 0

自连接方案 #

用自连接的方式把目标行的数据关联过来，逻辑更清晰，适合处理复杂匹配场景：

SELECT 
    si.[No_], 
    si.[Document No_], 
    si.Quantity, 
    si.[Line Discount _], 
    si.[Unit Price],
    si_zero.Quantity AS new_column
FROM [Sales Invoice Lines] si
LEFT JOIN [Sales Invoice Lines] si_zero
    ON si.[No_] = si_zero.[No_]
    AND CONVERT(decimal, si_zero.[Unit Price]) = 0
WHERE CONVERT(decimal, si.[Unit Price]) != 0

窗口函数方案（SQL Server 2012+） #

如果每个No_下只有一行单价0的记录，用窗口函数效率更高，能一次性计算出分组内的目标值：

SELECT 
    [No_], 
    [Document No_], 
    Quantity, 
    [Line Discount _], 
    [Unit Price],
    MAX(CASE WHEN CONVERT(decimal, [Unit Price]) = 0 THEN Quantity END) OVER(PARTITION BY [No_]) AS new_column
FROM [Sales Invoice Lines] si
WHERE CONVERT(decimal, si.[Unit Price]) != 0

注意事项 #

• 确认CONVERT(decimal, [Unit Price])的精度和你的数据类型匹配，防止转换出错
• 若同一个No_下存在多行单价0的记录，需明确取值规则，比如添加ORDER BY到TOP 1子句，或用MAX/MIN聚合
• 要是需要保留所有行（包括单价0的行），去掉WHERE条件即可，此时单价0的行的new_column会显示自身的Quantity（窗口函数方案）或匹配值

内容的提问来源于stack exchange，提问作者Luis Jacobo