如何无需枚举列即可按子表所有列分组？Postgres方案咨询

标准SQL情况 #

标准SQL并没有提供类似COLUMNS(t1)这种直接引用整张表所有列的语法，必须显式列出所有需要作为分组键的列。虽然部分数据库厂商有自己的扩展语法，但不属于SQL标准范畴。

PostgreSQL解决方案 #

在PostgreSQL中有两种实用的方法可以避免枚举所有列：

方法1：直接使用表别名加* #

PostgreSQL支持在GROUP BY子句中使用t1.*来代表表t1的所有列，同时在SELECT子句中也可以用t1.*来获取这些列，只要非聚合列与分组键完全对应即可。

针对你的需求，查询可以改写为：

SELECT t1.*, t2.col_k, COUNT(*)
FROM t1
JOIN t2 ON t2.col1 = t1.col1
WHERE t2.col1 > 0
GROUP BY t1.*, t2.col_k;

这个写法会自动将t1的所有列作为分组条件，同时返回这些列的值，完全省去了枚举列名的繁琐。

方法2：动态生成SQL语句 #

如果需要更灵活的场景（比如需要动态适配表结构变化），可以利用PostgreSQL的系统表和动态SQL功能生成查询语句。

方式A：手动生成列名（psql环境）

在psql命令行中，先执行以下语句获取t1的所有列名：

SELECT string_agg(quote_ident(column_name), ', ') 
FROM information_schema.columns 
WHERE table_name = 't1' AND table_schema = 'public'; -- 替换为你的表所属schema

将返回的列名字符串复制到原查询的SELECT和GROUP BY子句中即可。

方式B：用PL/pgSQL函数自动执行

创建一个函数来动态生成并执行查询，无需手动复制列名：

CREATE OR REPLACE FUNCTION get_grouped_data() 
RETURNS TABLE (t1_row t1, col_k t2.col_k%TYPE, count bigint) AS $$
DECLARE
    t1_columns text;
BEGIN
    SELECT string_agg(quote_ident(column_name), ', ') 
    INTO t1_columns
    FROM information_schema.columns
    WHERE table_name = 't1' AND table_schema = 'public';

    RETURN QUERY EXECUTE format(
        'SELECT t1.*, t2.col_k, COUNT(*)
         FROM t1
         JOIN t2 ON t2.col1 = t1.col1
         WHERE t2.col1 > 0
         GROUP BY t1.*, t2.col_k'
    );
END;
$$ LANGUAGE plpgsql;

调用该函数即可获取结果：

SELECT * FROM get_grouped_data();

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