多重选择语句SQL返回2列:如何将它们相互添加

Question

我有以下查询:

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

返回如下结果:

requestedParts  requestedLabour
-------------------------------
144.000          291.000000

现在，我想将这两列相互添加并创建第三列，称为total，其中添加了requestedParts和requestedLabour，因此在这种情况下它将是total: 435.000。有人知道这是可能的吗?

Answer 1

您可以将这两个子查询放在FROM子句中并交叉连接它们。

如果没有看到您的模式，我无法判断，但您甚至可以将两者合并为单个聚合

我还删除了窗口函数，因为在这个实例中使用标准聚合得到相同的结果

SELECT
rp.requestedParts,
rl.requestedLabour,
rp.requestedParts + rl.requestedLabour AS total
FROM
(
SELECT SUM (Price) 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 rp
CROSS JOIN
(
SELECT SUM(Cost * nCost) 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 rl;

Answer 2

这是我的想法。你可以这样写

SELECT  Result.requestedLabour,Result.requestedParts,
Result.requestedLabour+Result.requestedParts as Total
FROM (
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

) as Result