SQL多SELECT语句返回两列时如何相加生成合计列total

解决方案 #

这个需求完全可以实现，以下是两种可行的调整方案：

方案1：直接相加计算（最简洁） #

直接在现有SELECT语句中新增第三列，将两个子查询的结果相加后命名为total，如果需要统一保留3位小数，可以通过CAST/ROUND函数控制精度：

SELECT 
    (SELECT TOP 1 SUM (Price) OVER () AS requestedParts
     FROM Claim
     INNER JOIN ClaimCrossTire ON Claim.ID = ClaimCrossTire.ClaimID
     INNER JOIN Tire ON ClaimCrossTire.TireID = Tire.ID
     WHERE Claim.ID = 386160) AS requestedParts,
    (SELECT TOP 1 SUM(Cost * nCost) OVER () AS requestedLabour
     FROM Claim c 
     INNER JOIN ClaimCrossCostItem ccci ON c.ID = ccci.ClaimID
     INNER JOIN CostItem ci ON ccci.CostItemID = ci.ID
     WHERE ci.CostItemTypeID = 1 AND ClaimID = 386160) AS requestedLabour,
    -- 新增第三列，两个子查询结果相加
    CAST(
        (SELECT TOP 1 SUM (Price) OVER () AS requestedParts
         FROM Claim
         INNER JOIN ClaimCrossTire ON Claim.ID = ClaimCrossTire.ClaimID
         INNER JOIN Tire ON ClaimCrossTire.TireID = Tire.ID
         WHERE Claim.ID = 386160) 
        +
        (SELECT TOP 1 SUM(Cost * nCost) OVER () AS requestedLabour
         FROM Claim c 
         INNER JOIN ClaimCrossCostItem ccci ON c.ID = ccci.ClaimID
         INNER JOIN CostItem ci ON ccci.CostItemID = ci.ID
         WHERE ci.CostItemTypeID = 1 AND ClaimID = 386160)
    AS DECIMAL(18,3)) AS total

执行后你将得到预期结果：

requestedParts  requestedLabour  total
-----------------------------------------
144.000          291.000000      435.000

方案2：通过CTE包装计算（可读性更高） #

如果不想重复写子查询逻辑，可以用CTE先拿到前两列的结果，再计算total列：

WITH claim_amount AS (
    SELECT 
        (SELECT TOP 1 SUM (Price) OVER () AS requestedParts
         FROM Claim
         INNER JOIN ClaimCrossTire ON Claim.ID = ClaimCrossTire.ClaimID
         INNER JOIN Tire ON ClaimCrossTire.TireID = Tire.ID
         WHERE Claim.ID = 386160) AS requestedParts,
        (SELECT TOP 1 SUM(Cost * nCost) OVER () AS requestedLabour
         FROM Claim c 
         INNER JOIN ClaimCrossCostItem ccci ON c.ID = ccci.ClaimID
         INNER JOIN CostItem ci ON ccci.CostItemID = ci.ID
         WHERE ci.CostItemTypeID = 1 AND ClaimID = 386160) AS requestedLabour
)
SELECT 
    requestedParts,
    requestedLabour,
    CAST(requestedParts + requestedLabour AS DECIMAL(18,3)) AS total
FROM claim_amount

这种方式后续需要调整前两列的计算逻辑时，只需要修改CTE内的代码即可，维护成本更低。

补充说明 #

你的原始查询中两个子查询都是针对指定ClaimID返回单个数值的标量子查询，不存在多行匹配的问题，因此直接相加的逻辑是完全安全的。

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