请原谅我问这个问题的方式不太得体,因为我是新来的博士后。。。
有以下两个表:
CREATE TABLE pub (
id int
, time timestamp
);
id time
1 1 2010-02-10 01:00:00
2 2 2011-02-10 01:00:00
3 3 2012-02-10 01:00:00
以及
CREATE TABLE val (
id int
, type text
, val int
);
id type val
1 1 A 1
2 1 B 2
3 1 C 3
4 2 A 4
5 2 B 5
6 3 D 6
我想得到以下输出(对于
id <= 2
) type 2010 2011
1 A 1 4
2 B 2 5
3 C 3 NULL
所以
type
是表val中所有类型的超集。空表示标签C没有值。
理想情况下,列标题是多年的时间。或者是身份证本身。。。
最佳答案
至少有两种方法可以做到这一点。
如果您的表没有很多类别,则可以使用CTE
WITH x AS (
SELECT type,
sum(val) FILTER (WHERE date_part('year', time) = 2010) AS "2010",
sum(val) FILTER (WHERE date_part('year', time) = 2011) AS "2011"
FROM pub AS p JOIN val AS v ON (v.id = p.id)
GROUP BY type
)
SELECT * FROM x
WHERE "2010" is NOT NULL OR "2011" IS NOT NULL
ORDER BY type
;
但是,如果有多个或动态类别,则必须使用交叉表:
CREATE EXTENSION tablefunc;
SELECT * FROM crosstab(
$$
SELECT type,
date_part('year', time)::text as time,
sum(val) AS val
FROM pub AS p JOIN val AS v ON (v.id = p.id)
GROUP BY type, 2
ORDER BY 1, 2
$$,
$$VALUES ('2010'::text), ('2011'), ('2012') $$
) AS ct (type text, "2010" int, "2011" int, "2012" int);
;
关于postgresql - Postgres-按年份绑定(bind)相同类型的结果-长到宽数据,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/42149376/