优化含多子查询的SQL语句：减少重复逻辑与子查询数量

Absolutely—those repeated subqueries aren't just messy, they're also probably making your query run slower than it needs to by hitting the StockCosts table multiple times. Let's refactor this to clean up the logic and cut down on redundant table access.

Key Issues with the Original Query #

• You're querying StockCosts 5 separate times for the same set of filters (Disabled = 0 + matching StockId)
• The TOP 1 without an ORDER BY is risky—if multiple records match a StockId + ChargeType, you'll get an arbitrary result (no guarantee which Cost is picked)
• Calculations like VendorChargesTotal repeat the VAT logic instead of reusing already computed values

Refactored Query (Cleaner & More Efficient) #

This version hits StockCosts only once, uses conditional aggregation to pull ChargeType-specific values, and reuses calculated values for totals:

WITH StockCostsCalculations AS (
    SELECT
        StockId,
        -- Get the desired Cost for ChargeType 1 & 2 (add ORDER BY to ROW_NUMBER for consistent results)
        MAX(CASE WHEN ChargeType = 1 AND rn = 1 THEN Cost END) AS VendorRecovery,
        MAX(CASE WHEN ChargeType = 2 AND rn = 1 THEN Cost END) AS VendorCommission,
        SUM(Cost) AS VendorChargesNet
    FROM (
        SELECT
            StockId,
            Cost,
            ChargeType,
            -- Assign row number to pick the top 1 record per StockId + ChargeType
            -- Replace /* YourSortColumnHere */ with a column like CostDate DESC or Id DESC
            ROW_NUMBER() OVER (PARTITION BY StockId, ChargeType ORDER BY /* YourSortColumnHere */ DESC) AS rn
        FROM StockCosts
        WHERE Disabled = 0
    ) sc
    GROUP BY StockId
)

SELECT
    s.StockId,
    ISNULL(scc.VendorRecovery, 0) AS VendorRecovery,
    ISNULL(scc.VendorCommission, 0) AS VendorCommission,
    ISNULL(scc.VendorChargesNet, 0) AS VendorChargesNet,
    -- Reuse precomputed net value for VAT instead of recalculating SUM(Cost)
    ISNULL((scc.VendorChargesNet / 100) * st.VATRate, 0) AS VendorChargesVAT,
    ISNULL(scc.VendorChargesNet + ((scc.VendorChargesNet / 100) * st.VATRate), 0) AS VendorChargesTotal
FROM SaleTrans st
INNER JOIN Stock s ON st.StockId = s.StockId
LEFT JOIN StockCostsCalculations scc ON s.StockId = scc.StockId

What Changed? #

1. Single Table Access: We wrap the StockCosts query in a CTE so we only scan the table once, not five times.
2. Controlled TOP 1: Using ROW_NUMBER() lets you specify exactly which record to pick for each ChargeType—fill in the ORDER BY with a column like CostDate or Id to ensure consistent results every time.
3. Conditional Aggregation: Instead of separate subqueries for each ChargeType, we use CASE WHEN inside MAX() to pull the specific values we need in one go.
4. Reused Calculations: VendorChargesTotal now uses the precomputed VendorChargesNet instead of recalculating SUM(Cost) again, making the query cleaner and faster.

Quick Simplification Tip #

If you're guaranteed only one valid record per StockId + ChargeType, you can remove the ROW_NUMBER() logic entirely and use MAX(CASE WHEN ChargeType = 1 THEN Cost END) directly in the CTE's select list.

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