使用PostgreSQL中的aggregate filter
子句。在db<>fiddle上查看演示:
SELECT
first_name,
id,
sum(total) FILTER (WHERE type = 'onsite') AS onsite,
sum(total) FILTER (WHERE type = 'remote') AS remote
FROM
test
GROUP BY
first_name,
id;
上述SQL语句的功能是从test
表中按first_name
和id
字段进行分组,然后分别计算每个分组中type
为'onsite'时的total
总和作为onsite
列的值,以及type
为'remote'时的total
总和作为remote
列的值。
另外,你也可以考虑使用PostgreSQL的crosstab()
函数。首先需要安装并启用tablefunc
扩展:
CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM crosstab(
$a$ SELECT first_name, id, type, total FROM people ORDER BY 1,2,3 $a$ ,
$b$ SELECT DISTINCT type FROM people ORDER BY 1 $b$
) AS ct(first_name text, id int, onsite int, remote int);