SQL Server中多列分组问题：排除指定列求均值

Hey there! I totally get the frustration of typing out dozens of column names for a GROUP BY clause—no one wants to do that. Let's break down how to solve this problem without manually listing every column except your target column (let's call it column_d).

First, you're right that GROUP BY doesn't support wildcards directly, but we can dynamically generate the list of columns to group by using your database's system catalogs. Here are practical solutions for the most common SQL dialects:

PostgreSQL Example #

PostgreSQL makes this straightforward with string aggregation and dynamic SQL. You can query the information_schema.columns view to get all columns except column_d, then build your GROUP BY clause automatically:

-- Step 1: Generate the list of columns to group by
SELECT string_agg(column_name, ', ') AS group_by_columns
FROM information_schema.columns
WHERE table_name = 'your_table_name'
  AND column_name != 'column_d';

-- Step 2: Plug the result into your final query
-- Replace [group_by_columns_result] with the output from step 1
SELECT [group_by_columns_result], AVG(column_d) AS avg_column_d
FROM your_table_name
GROUP BY [group_by_columns_result];

For full automation, use a PL/pgSQL block to run everything in one go:

DO $$
DECLARE
    group_by_cols TEXT;
BEGIN
    SELECT string_agg(column_name, ', ') INTO group_by_cols
    FROM information_schema.columns
    WHERE table_name = 'your_table_name'
      AND column_name != 'column_d';

    EXECUTE format('SELECT %s, AVG(column_d) AS avg_column_d FROM your_table_name GROUP BY %s', group_by_cols, group_by_cols);
END $$;

MySQL Example #

MySQL uses prepared statements for dynamic SQL. Here's how to implement it:

-- Step 1: Generate the column list
SET @group_by_cols = (
    SELECT GROUP_CONCAT(column_name SEPARATOR ', ')
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'your_table_name'
      AND column_name != 'column_d'
);

-- Step 2: Prepare and run the query
SET @sql = CONCAT('SELECT ', @group_by_cols, ', AVG(column_d) AS avg_column_d FROM your_table_name GROUP BY ', @group_by_cols);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server Example #

In SQL Server, leverage the sys.columns catalog view and dynamic SQL:

DECLARE @group_by_cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- Generate the quoted column list (handles special characters)
SELECT @group_by_cols = STRING_AGG(QUOTENAME(name), ', ')
FROM sys.columns
WHERE object_id = OBJECT_ID('your_table_name')
  AND name != 'column_d';

-- Build and execute the final query
SET @sql = N'SELECT ' + @group_by_cols + N', AVG(column_d) AS avg_column_d FROM your_table_name GROUP BY ' + @group_by_cols;
EXEC sp_executesql @sql;

Quick Ad-Hoc Alternative #

If you just need a one-off solution and don't want to use dynamic SQL:

1. Run the column list query from the examples above
2. Copy the result into your SELECT and GROUP BY clauses
3. (The queries already exclude column_d, so no extra cleanup needed!)

Just remember to replace your_table_name and column_d with your actual table and column names. If your column names have spaces or special characters, use quoted identifiers (like QUOTENAME in SQL Server or double quotes in PostgreSQL) to avoid syntax errors.

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