sql - 将 postgres 交叉表查询转换为 SQL

Question

我想要实现以下目标:

存储不同语言翻译的数据库。通过单个查询获取所需语言的所有字符串，如果该翻译不存在，则使用第二好的语言作为后备等。对于每种语言，后备可能有所不同(例如 FR-DE-EN 与 DE-EN-FR) 。

我已经通过交叉表查询使用 pgsql 实现了它，并希望将其转换为 SQL Server，但有点卡在那里。我认为PIVOT将是实现我想要的结果的语言功能，但尚未弄清楚如何正确使用它。

MWE 定义和测试数据:

-- load tablefunc extension for crosstab
drop extension if exists tablefunc;
create extension tablefunc;
-- crosstab only allows single column - define int and varchar tuples for this purpose
DROP TYPE IF EXISTS intT; CREATE TYPE intT AS  (module int, id int );
DROP TYPE IF EXISTS strT; CREATE TYPE strT AS (lang varchar, txt varchar);

drop table if exists texts;
drop table if exists langs;

create table texts
(   module int not null
,   id int not null
,   lang varchar not null
,   txt varchar not null);

create table langs -- for each language (first) store up to 3 languages (lang) and their priority (lower = would be used first)
(   first varchar not null
,   lang varchar not null
,   priority int not null);

insert into texts (module, id, lang, txt) values
    (0,0,'def','HelloDEF'),
    (0,1,'def','WorldDEF'),
    (0,0,'en','Hello'),
    (0,1,'en','World'),
    (0,0,'de','Hallo'),
    (0,1,'de','Welt'),
    (0,0,'jp','Konnichiwa'),
    (0,1,'fr','Monde'),
    (1,0,'def','Switzerland'),
    (1,0,'de','Schweiz'),
    (1,0,'fr','Suisse'),
    (1,0,'jp','Suisu');

insert into langs (first, lang, priority) values
    ('jp','jp',0),
    ('jp','en',1),
    ('jp','def',2),
    ('en','en',0),
    ('en','def',1),
    ('en','def',2),
    ('de','de',0),
    ('de','en',1),
    ('de','def',2),
    ('fr','fr',0),
    ('fr','de',1),
    ('fr','def',2);

查询(pgsql):

select (mod_id).*, (coalesce(a,b,c)).* -- unpack tuple types here to get nice table
from crosstab($$
    select (module,id) as mod_id, priority, (lang,txt) as lang_txt
    from texts
    join langs using (lang)
    where first = 'fr' --! language goes here
    and module = 0 --! module integer goes here
    order by id, priority asc
$$,$$
    select generate_series(0,2) -- always return 0,1,2 here.
$$) as ct (mod_id intT, a strT, b strT, c strT);

输出:

 module | id | lang |  txt
--------+----+------+-------
      0 |  0 | de   | Hallo
      0 |  1 | fr   | Monde

Answer 1

据我了解这个问题，这个结果可以使用标准 SQL 来实现，而不需要旋转数据。简单ROW_NUMBER()应该足够了。下面的查询适用于 SQL Server 和 Postgres。

示例数据

create table #texts
(   module int not null
,   id int not null
,   lang varchar(50) not null
,   txt varchar(50) not null);

create table #langs -- for each language (first) store up to 3 languages (lang) and their priority (lower = would be used first)
(   first varchar(50) not null
,   lang varchar(50) not null
,   priority int not null);

insert into #texts (module, id, lang, txt) values
    (0,0,'def','HelloDEF'),
    (0,1,'def','WorldDEF'),
    (0,0,'en','Hello'),
    (0,1,'en','World'),
    (0,0,'de','Hallo'),
    (0,1,'de','Welt'),
    (0,0,'jp','Konnichiwa'),
    (0,1,'fr','Monde'),
    (1,0,'def','Switzerland'),
    (1,0,'de','Schweiz'),
    (1,0,'fr','Suisse'),
    (1,0,'jp','Suisu');

insert into #langs (first, lang, priority) values
    ('jp','jp',0),
    ('jp','en',1),
    ('jp','def',2),
    ('en','en',0),
    ('en','def',1),
    ('en','def',2),
    ('de','de',0),
    ('de','en',1),
    ('de','def',2),
    ('fr','fr',0),
    ('fr','de',1),
    ('fr','def',2);

查询

我已接受您的内部查询并添加了 ROW_NUMBER那里。很明显，我们只需要选择具有最高 priority 的行。对于每个 id (这就是 PARTITION BY id 定义中有 ORDER BY priority 和 ROW_NUMBER 的原因)。如果您想要多个结果module一次，而不仅仅是一个特定模块，然后添加 module至PARTITION BY条款。

SELECT
    #texts.module
    ,#texts.id
    ,#langs.priority
    ,#langs.lang
    ,#texts.txt
    ,ROW_NUMBER() OVER (PARTITION BY #texts.id ORDER BY #langs.priority) AS rn
FROM
    #texts
    INNER JOIN #langs ON #langs.lang = #texts.lang
WHERE
    #langs.first = 'fr' --! language goes here
    AND #texts.module = 0 --! module integer goes here
ORDER BY
    #texts.id, #langs.priority asc
;

结果

+--------+----+----------+------+----------+----+
| module | id | priority | lang |   txt    | rn |
+--------+----+----------+------+----------+----+
|      0 |  0 |        1 | de   | Hallo    |  1 |
|      0 |  0 |        2 | def  | HelloDEF |  2 |
|      0 |  1 |        0 | fr   | Monde    |  1 |
|      0 |  1 |        1 | de   | Welt     |  2 |
|      0 |  1 |        2 | def  | WorldDEF |  3 |
+--------+----+----------+------+----------+----+

最终查询

WITH
CTE
AS
(
    SELECT
        #texts.module
        ,#texts.id
        ,#langs.priority
        ,#langs.lang
        ,#texts.txt
        ,ROW_NUMBER() OVER (PARTITION BY #texts.id ORDER BY #langs.priority) AS rn
    FROM
        #texts
        INNER JOIN #langs ON #langs.lang = #texts.lang
    WHERE
        #langs.first = 'fr' --! language goes here
        AND #texts.module = 0 --! module integer goes here
)
SELECT
    module
    ,id
    ,lang
    ,txt
FROM CTE
WHERE rn = 1
ORDER BY
    id
;

结果

+--------+----+------+-------+
| module | id | lang |  txt  |
+--------+----+------+-------+
|      0 |  0 | de   | Hallo |
|      0 |  1 | fr   | Monde |
+--------+----+------+-------+

清理

drop table #texts;
drop table #langs;