用于将模板中的多个值从多个表替换为多个表的SQL查询

老实说,我会创建一个小函数,在这个函数中循环遍历 state_property 并且累积地将找到的通配符字符串替换为其文本。

但是我在一个查询中解决了它。我不确定它是否与所有特殊情况都匹配,但在您的示例中,它是有效的:

demo:db<>fiddle

SELECT
    string_agg(                                                           -- 8
        regexp_replace(split_key, '^.*\}', value),                        -- 7
        '' ORDER BY row_number
    )
FROM (
    SELECT
        s.id,
        sp.value,
        substring(key, 3) as s_key,                                       -- 5
        split_table.*
    FROM translation t
    JOIN statechange sc ON t.messageid = sc.completemessagetemplateid     -- 1
    JOIN state s ON s.id = sc.state_id
    JOIN state_stateproperty ssp ON s.id = ssp.state_id
    JOIN stateproperty sp ON ssp.stateproperties_id = sp.id
    JOIN translation stnme ON s.nameid = stnme.messageid
    CROSS JOIN                                        
        regexp_split_to_table(                                            -- 3   
            -- 2
            replace(t.messagetranslation, '${state}', stnme.messagetranslation),
            '\$\{'
        ) WITH ORDINALITY as split_table(split_key, row_number)           -- 4
    WHERE t.language = 'en'
) s
WHERE position(s_key in split_key) = 0 and split_key != ''                -- 6
GROUP BY id                                                               -- 8

1. 简单地将表连接在一起(下一次您可以稍微简化您的示例,这样我们就不必创建这些不同的表。我确信你知道如何加入)
2. 几乎不能取代 ${state} 状态变量 nameid
3. 这将每次拆分模板字符串 ${ 找到字符串。所以它创建了一个新行,该行以某个通配符开头。注意 ${firstName} 将成为 firstName} 因为正在删除字符串分隔符。
4. 添加一个行计数,以获取在稍后汇总行时如何排序的条件(8)。 WITH ORDINALITY 仅作为 FROM 子句,所以在这里添加了一个join的整个函数。
5. 因为(3)我剥去了 ${ 钥匙上也有一部分。所以以后可以更好地进行分析和比较(6)
6. 因为(3)创建了太多的行(交叉联接),所以我只希望在 key 是拆分字符串的第一个通配符。其他人都错了。
7. 现在我用这个键替换通配符
8. 因为每行只有一个通配符,所以需要再次将它们合并到一个字符串中(按 state_id )实现正确的顺序,我们使用的是(5)中的行号