如何用SQL实现按Bank分组显示明细、小计及累计总和行

Got it, let's tackle this requirement step by step. You need to keep all detail rows, add subtotals every time the bank value changes, and include a final grand total. Here's how to do it using SQL Server's GROUPING SETS feature, which lets you combine multiple grouping levels in one query:

Query Code #

SELECT
    -- Customize the bank display for subtotal/grand total rows
    CASE
        WHEN GROUPING(bank) = 1 THEN '[Grand Total]'
        WHEN GROUPING(account) = 1 THEN CONCAT('[Subtotal - ', bank, ']')
        ELSE bank
    END AS bank,
    -- Clear the account column for summary rows
    CASE
        WHEN GROUPING(account) = 1 THEN ''
        ELSE account
    END AS account,
    -- Calculate sums (detail rows will show their original values)
    SUM(value1) AS value1,
    SUM(value2) AS value2,
    SUM(value3) AS value3
FROM #MyTable
GROUP BY GROUPING SETS (
    (bank, account), -- Include all original detail rows
    (bank),          -- Add subtotals for each unique bank
    ()               -- Add a grand total for all rows
)
-- Ensure logical ordering: details first, then subtotal, then grand total
ORDER BY
    -- Push grand total to the very end
    CASE WHEN GROUPING(bank) = 1 THEN 2 ELSE 1 END,
    bank,
    -- Show detail rows before their bank's subtotal
    CASE WHEN GROUPING(account) = 1 THEN 1 ELSE 0 END;

Key Explanations #

• GROUPING SETS: This clause combines three result sets into one:
  1. (bank, account): Returns every original detail row (since grouping by both columns preserves each unique account entry)
  2. (bank): Generates subtotal rows for each bank, summing value1, value2, and value3 across all accounts in that group
  3. (): Creates a single grand total row that sums all values across the entire table
• GROUPING() Function: This checks if a column is being aggregated in the current row. A value of 1 means the row is a summary for that column, while 0 means it's a detail row. We use this to label subtotals and grand totals clearly for readability.
• ORDER BY: Makes sure the output flows logically: detail rows appear first for each bank, followed by the bank's subtotal, and the grand total sits at the bottom of the result set.

Example Output #

Using your sample data (plus the NOSPA entry), the output would look like this:

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