SQL Server拆分配送费税费及子查询多行结果报错问题求助

Got it, let's tackle this problem step by step. First, let's unpack why you're hitting that "Subquery returned more than 1 value" error, then build the correct SQL to get your desired output.

Why the Error Happens #

That error pops up when a subquery you’re using returns multiple rows, but your outer query expects a single value (like when you use = instead of an aggregated result or window function). We’ll avoid this by calculating order-level metrics cleanly with either window functions or a grouped CTE.

Calculation Logic Breakdown #

From your example, here’s what we need to compute for each row:

1. TotalTax: The sum of all transaction taxes in the order plus the total delivery charge tax (e.g., S1: 0+4+1.5=5.5; S2:2.5+2+2.5=7)
2. Delivery Charge Tax: The total delivery charge tax for the order, split equally among all transactions in the order (e.g., S1:1.5/2=0.75; S2:2.5/2=1.25)

Solution 1: Using Window Functions (Concise) #

This approach calculates order-level values directly in the SELECT clause, no subqueries needed:

SELECT
    SaleOrder,
    Transaction,
    Amount,
    Tax,
    [Delivery Charge] AS DeliveryCharge,
    -- Grab the order-level TotalTax (since it's repeated per order, MAX/MIN works)
    MAX([TotalTax (Including Tax of Delivery Charge)]) OVER (PARTITION BY SaleOrder) AS TotalTax,
    -- Calculate per-row Delivery Charge Tax
    (
        MAX([TotalTax (Including Tax of Delivery Charge)]) OVER (PARTITION BY SaleOrder)
        - SUM(Tax) OVER (PARTITION BY SaleOrder)
    ) / COUNT(Transaction) OVER (PARTITION BY SaleOrder) AS [Delivery Charge Tax]
FROM YourTableName; -- Replace with your actual table name

Solution 2: Using a CTE (Explicit Grouping) #

If you prefer a more readable grouped summary first, use a Common Table Expression to precompute order totals, then join back to your original data:

WITH OrderTotals AS (
    SELECT
        SaleOrder,
        COUNT(Transaction) AS TransactionCount,
        SUM(Tax) AS TotalTransactionTax,
        MAX([TotalTax (Including Tax of Delivery Charge)]) AS OrderTotalTax
    FROM YourTableName
    GROUP BY SaleOrder
)
SELECT
    yt.SaleOrder,
    yt.Transaction,
    yt.Amount,
    yt.Tax,
    yt.[Delivery Charge] AS DeliveryCharge,
    ot.OrderTotalTax AS TotalTax,
    (ot.OrderTotalTax - ot.TotalTransactionTax) / ot.TransactionCount AS [Delivery Charge Tax]
FROM YourTableName yt
JOIN OrderTotals ot ON yt.SaleOrder = ot.SaleOrder;

Verification of Results #

Let’s cross-check against your desired output:

• For SaleOrder S1:
  • Total transaction tax = 0 + 4 = 4
  • Order TotalTax = 5.5
  • Total delivery charge tax = 5.5 - 4 = 1.5
  • Per-row Delivery Charge Tax = 1.5 / 2 = 0.75 ✅
• For SaleOrder S2:
  • Total transaction tax = 2.5 + 2 = 4.5
  • Order TotalTax =7
  • Total delivery charge tax =7 -4.5=2.5
  • Per-row Delivery Charge Tax=2.5/2=1.25 ✅

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