我有一个如下所示的架构，我想运行一个查询，在该查询中，我在 points 表的每一行的输出中获得一列.

I have a schema as show like the below, and I want to run a query where I get a column in the output for every row of the points table.

因此，对于每个 usage 行，我想将使用量的 amount 乘以所引用 points_id 的 amount代码>，然后总结并按人分组.因此，对于示例数据，我希望输出如下所示:

So for each usage row I want to multiply the amount of the usage times the amount for the referenced points_id, and then sum that up and group by person. So for the example data I'd want output that looked like this:

 Name  | foo  | bar  | baz  
-------|------|------|------
 Scott | 10.0 | 24.0 | 0.0  
 Sam   | 0.0  | 0.0  | 46.2   

这是架构/数据:

CREATE TABLE points (
   ident int primary key NOT NULL,
   abbrev VARCHAR NOT NULL,
   amount real NOT NULL
);

CREATE TABLE usage (
  ident int primary key NOT NULL,
  name VARCHAR NOT NULL,
  points_id integer references points (ident),
  amount real
);

INSERT INTO points (ident, abbrev, amount) VALUES
  (1, 'foo', 1.0),
  (2, 'bar', 2.0),
  (3, 'baz', 3.0);

INSERT INTO usage (ident, name, points_id, amount) VALUES 
  (1, 'Scott', 1, 10),
  (2, 'Scott', 2, 12),
  (3, 'Sam', 3, 3.4),
  (4, 'Sam', 3, 12);

我使用的是 PostgreSQL 9.2.8

I'm using PostgreSQL 9.2.8

数据只是样本.真正的 usage 表中有数千行，而 points 表中可能有十几行.这里的真正意图是我不想硬编码所有 points 求和，因为我在许多函数中使用它们.

The data is just sample. There are thousands of rows in the real usage table and probably a dozen in the points table. The real intent here is I don't want to hardcode all the points summations as I use them in many functions.

推荐答案

select 
t1.name,
sum(case when t2.abbrev='foo' then t1.amount*t2.amount else 0 end) as foo,
sum(case when t2.abbrev='bar' then t1.amount*t2.amount else 0 end) as bar,
sum(case when t2.abbrev='baz' then t1.amount*t2.amount else 0 end) as baz
from usage t1 inner join points t2 on t1.points_id=t2.ident
group by t1.name;

SQL 小提琴示例:http://sqlfiddle.com/#!15/cc84a/6;

SQL Fiddle Example:http://sqlfiddle.com/#!15/cc84a/6;

对动态情况使用以下 PostgreSQL 函数:

Use following PostgreSQL function for dynamic cases:

create or replace function sp_test()
returns void as
$$

declare cases character varying;
declare sql_statement text;
begin

select string_agg(concat('sum(case when t2.abbrev=','''',abbrev,'''',' then t1.amount*t2.amount else 0 end) as ', abbrev),',') into cases from points;

drop table if exists temp_data;

sql_statement=concat('create temporary table temp_data as select 
t1.name,',cases ,' 
from usage t1 inner join points t2 on t1.points_id=t2.ident
group by t1.name ');

execute sql_statement;

end;
$$
language 'plpgsql';

函数使用临时表来存储动态列数据.

通过以下方式调用函数获取数据:

Call function in following way to get data:

select * from sp_test();select * from temp_data;