SQL Server交叉连接查询：请求协助完善给定的Union All查询语句

看起来你是要生成指定日期的现金收支报表，包含期初余额、每笔现金流入/流出记录以及实时累计余额。我帮你优化了原查询的几个问题，比如避免不必要的交叉连接、实现正确的累计余额计算，同时让逻辑更清晰：

WITH OpeningBalance AS (
    -- 获取唯一的期初余额（假设Setting表只有一行有效数据，或者取最新/指定的期初）
    SELECT TOP 1 Opening 
    FROM dbo.Setting 
    WHERE Opening != 0
),
CashTransactions AS (
    -- 现金流入记录
    SELECT 
        CONVERT(Varchar(20), ci.EDate, 105) AS Date,
        om.LocName AS Descriptions,
        ci.Amount AS credit,
        0.00 AS debit,
        ci.Amount AS TransactionAmount
    FROM dbo.CashInward ci
    INNER JOIN dbo.OutletMst om ON om.LocId = ci.OutletId
    WHERE 
        ci.EDate = CONVERT(Datetime, '03/Apr/2018', 105)
        AND ci.deletes != 1
        AND om.Active = 1
        AND ci.Amount != 0

    UNION ALL

    -- 现金流出记录（如果你需要包含这部分的话，可根据实际情况调整）
    SELECT 
        CONVERT(Varchar(20), co.EDate, 105) AS Date,
        om.LocName AS Descriptions,
        0.00 AS credit,
        co.Amount AS debit,
        -co.Amount AS TransactionAmount
    FROM dbo.CashOutward co
    INNER JOIN dbo.OutletMst om ON om.LocId = co.OutletId
    WHERE 
        co.EDate = CONVERT(Datetime, '03/Apr/2018', 105)
        AND co.deletes != 1
        AND om.Active = 1
        AND co.Amount != 0
)
-- 组合期初余额和交易记录，计算累计余额
SELECT 
    -- 期初余额行的日期留空
    CASE WHEN ct.Date IS NULL THEN '' ELSE ct.Date END AS Date,
    CASE WHEN ct.Date IS NULL THEN 'Opening Balance' ELSE ct.Descriptions END AS Descriptions,
    ISNULL(ct.credit, 0.00) AS credit,
    ISNULL(ct.debit, 0.00) AS debit,
    -- 用窗口函数计算累计余额：期初余额 + 之前所有交易的金额总和
    ob.Opening + SUM(ct.TransactionAmount) OVER (ORDER BY (SELECT NULL)) AS Balance
FROM OpeningBalance ob
LEFT JOIN CashTransactions ct ON 1=1
ORDER BY 
    -- 确保期初余额排在最前面，然后按交易日期/顺序排列
    CASE WHEN ct.Date IS NULL THEN 0 ELSE 1 END,
    ct.Date;

关键优化点说明： #

• 避免交叉连接问题：用CTE OpeningBalance 获取唯一的期初余额，替代原查询中cross join setting可能导致的重复数据问题（如果Setting表有多行的话）。
• 累计余额计算：使用SUM() OVER (ORDER BY ...)窗口函数实现正确的累计余额，而不是简单的Opening+Amount，这样每一行的余额都是期初加上之前所有交易的总和。
• 扩展性：添加了现金流出的模板（CashOutward部分），你可以根据实际业务需求保留或删除这部分。
• 逻辑清晰：用CTE拆分不同逻辑块，让查询更容易维护和理解。
• 排序控制：通过ORDER BY确保期初余额始终排在报表最顶部，交易记录按日期排序（如果需要更精确的排序，可以添加交易ID或时间字段）。

如果你的Setting表有多行期初数据，或者需要按不同门店区分期初余额，可以告诉我更多细节，我再帮你调整查询逻辑。

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