我有以下查詢：
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, whererequestedParts和requestedLabourare added 所以在這種情況下它將是total: 435.000. 有誰知道這是否可能？
uj5u.com熱心網友回復：
您可以將這兩個子查詢放在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;
uj5u.com熱心網友回復：
這是我的想法。你可以寫這些行
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
uj5u.com熱心網友回復：
也許將這些子查詢放在一個外部選擇陳述句中，該陳述句還列出了 requestsParts、requestetLabour 和 total？
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/316296.html
標籤：sql sql-server