如何将两张表的数据转为行列交叉的单表视图？

没问题，要把你的两张表转换成以对象为行列、multiplier为单元格值的透视表，PostgreSQL里可以借助tablefunc扩展的crosstab函数来实现，下面分静态和动态两种场景给你详细方案：

1. 先启用必要的扩展 #

首先需要开启PostgreSQL的tablefunc扩展（默认可能没启用），执行这条语句：

CREATE EXTENSION IF NOT EXISTS tablefunc;

2. 静态透视表（已知所有对象名称） #

如果你的objects表中的对象是固定的（比如你提到的Comps、Products），可以直接写静态查询：

SELECT *
FROM crosstab(
  -- 第一步：获取源数据（行对象名、列对象名、关联值）
  'SELECT o1.value AS row_obj, o2.value AS col_obj, r.multiplier
   FROM relation r
   JOIN objects o1 ON r.id1 = o1.id
   JOIN objects o2 ON r.id2 = o2.id
   ORDER BY 1, 2',
  -- 第二步：定义透视表的列（所有对象名称）
  'SELECT DISTINCT value FROM objects ORDER BY 1'
) AS ct(row_obj text, "Comps" integer, "Products" integer);

说明： #

• 第一个SQL参数负责关联两张表，把ID转换成对应的对象名称，同时取出multiplier；
• 第二个SQL参数用来生成透视表的列名，从objects表中去重获取所有对象；
• 如果需要把无关联的单元格显示为0而不是NULL，可以用COALESCE包裹对应的列，比如COALESCE("Comps", 0)。

3. 动态透视表（对象会动态新增） #

如果objects表中的对象会随时新增，静态查询需要手动修改列名，这时候可以用动态SQL来自动生成列：

DO $$
DECLARE
  col_list text;
BEGIN
  -- 生成列名列表（比如 "Comps" integer, "Products" integer, ...）
  SELECT string_agg(DISTINCT quote_ident(value) || ' integer', ', ')
  INTO col_list
  FROM objects;

  -- 执行动态透视查询
  EXECUTE format(
    'SELECT *
     FROM crosstab(
       ''SELECT o1.value AS row_obj, o2.value AS col_obj, r.multiplier
        FROM relation r
        JOIN objects o1 ON r.id1 = o1.id
        JOIN objects o2 ON r.id2 = o2.id
        ORDER BY 1, 2'',
       ''SELECT DISTINCT value FROM objects ORDER BY 1''
     ) AS ct(row_obj text, %s)',
    col_list
  );
END $$;

说明： #

• 这段PL/pgSQL代码会自动从objects表中读取所有对象名称，生成对应的列定义；
• quote_ident用来处理对象名称包含特殊字符的情况，避免SQL语法错误；
• 执行后会直接输出最新的透视表结果，无需手动维护列名。

补充：测试数据示例 #

如果需要测试，可以插入一些示例数据：

-- 插入对象
INSERT INTO objects (value) VALUES ('Comps'), ('Products'), ('Services');
-- 插入关联关系
INSERT INTO relation (id1, id2, multiplier) VALUES
(1, 2, 5),
(2, 1, 3),
(1, 3, 2);

执行静态或动态查询后，就能得到类似这样的结果：

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