动态获取字段值分布的SQL查询

SELECT field0, 
       COUNT(*) 
FROM   table 
GROUP  BY field0 

如果您希望在代码中显示列结果。对于某些DB品牌,有PIVOT功能。

与 Postgres 你可以这样做:

select t.name as column_name, 
       sum(val::int) as sum
from data d, jsonb_each_text(to_jsonb(d) - 'id') as t(name, val)
group by t.name;

这个 - 'id' 删除 id 来自生成的JSON的属性。在聚合中只包含某些列的另一个选项是添加 where

select column_name,
       sum(val::int) as sum
from (       
  select t.name as column_name, 
         t.val
  from data d, jsonb_each_text(to_jsonb(d)) as t(name, val)
) t
where column_name like 'col%'
group by column_name;

使用以下示例表:

create table data 
(
  id serial primary key,
  col1 int,
  col2 int,
  col3 int,
  col4 int,
  col5 int
);

insert into data (col1, col2, col3, col4, col5)
values 
(1, 2, 3, 4, 5),
(6, 7, 8, 9, 10),
(11, 12, 13, 14, 15);

查询将返回:

column_name | sum
------------+----
col1        |  18
col2        |  21
col5        |  30
col4        |  27
col3        |  24