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

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

标签： sqlsql-servertsqlpivot

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 idandORDER BY priority的原因ROW_NUMBER）。如果您想要module一次获得多个 s 的结果，而不仅仅是一个特定模块，则添加moduletoPARTITION 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;