PostgreSQL交叉表/透视表创建求助：关联三表后fk_id结果异常

问题根源 #

你的原查询仅关联了model_ex与model的匹配记录，且按model_ex.id分组，导致结果行与model_ex记录一一对应，无法实现每个fk_id下包含所有model记录的需求。

解决思路 #

要生成目标结构的交叉表，需先构建所有唯一fk_id与所有model记录的笛卡尔积，确保每个fk_id都能关联到每一条model数据；之后关联datatype获取对应编码，最后通过透视转换为目标格式并替换空值为'n'。

最终SQL代码 #

WITH all_fk AS (
    -- 提取所有唯一的fk_id
    SELECT DISTINCT fk_id FROM model_ex
),
all_model_fk AS (
    -- 生成fk_id与所有model记录的笛卡尔积，确保每个fk_id覆盖全部model数据
    SELECT 
        af.fk_id,
        m.id AS model_id,
        m.datatype_id,
        m."values"
    FROM all_fk af
    CROSS JOIN model m
),
data AS (
    -- 关联datatype获取编码，并生成自增的最终id
    SELECT 
        ROW_NUMBER() OVER (ORDER BY amf.fk_id, amf.model_id) AS id,
        amf.fk_id,
        d.code,
        amf."values"
    FROM all_model_fk amf
    JOIN datatype d ON d.id = amf.datatype_id
)
SELECT 
    id,
    fk_id,
    -- 透视并将空值替换为'n'，同时统一为文本格式
    COALESCE(MAX("values") FILTER (WHERE code = 'Q_1')::TEXT, 'n') AS Q_1,
    COALESCE(MAX("values") FILTER (WHERE code = 'Q_2')::TEXT, 'n') AS Q_2,
    COALESCE(MAX("values") FILTER (WHERE code = 'Q_3')::TEXT, 'n') AS Q_3,
    COALESCE(MAX("values") FILTER (WHERE code = 'Q_4')::TEXT, 'n') AS Q_4,
    COALESCE(MAX("values") FILTER (WHERE code = 'Q_5')::TEXT, 'n') AS Q_5,
    COALESCE(MAX("values") FILTER (WHERE code = 'Q_6')::TEXT, 'n') AS Q_6,
    COALESCE(MAX("values") FILTER (WHERE code = 'Q_7')::TEXT, 'n') AS Q_7,
    COALESCE(MAX("values") FILTER (WHERE code = 'Q_8')::TEXT, 'n') AS Q_8,
    COALESCE(MAX("values") FILTER (WHERE code = 'Q_9')::TEXT, 'n') AS Q_9,
    COALESCE(MAX("values") FILTER (WHERE code = 'Q_10')::TEXT, 'n') AS Q_10
FROM data
GROUP BY id, fk_id
ORDER BY id;

关键步骤说明 #

1. all_fk CTE：从model_ex中提取所有唯一的fk_id，确保覆盖所有需要的分组维度。
2. all_model_fk CTE：通过CROSS JOIN实现每个fk_id与所有model记录的关联，这是实现目标结构的核心操作。
3. data CTE：关联datatype表获取对应的问题编码，并通过ROW_NUMBER()生成自增的id，匹配你期望的结果格式。
4. 主查询：使用FILTER子句进行列透视，通过COALESCE将空值转换为'n'，同时将数值类型转为文本以统一输出格式。

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