PostgreSQL选择结果具有不同的ID，可从另一个表中使用特定值

Question

我有一个一些_dictionary表和一个带有多种语言的数据的some_dictionary_language表（以及对某些_dictionary的引用）。

我需要在soming_dictionary.id上获得唯一的值，而更喜欢给定语言的结果，如果不是默认语言。

给定的SQL：

create table some_dictionary(
    id bigserial primary key,
    some_text varchar(5),
    some_array integer[]
);

create table some_dictionary_language(
    id bigserial primary key,
    some_dictionary_id bigint not null REFERENCES some_dictionary(id),
    language varchar(64) not null,
    name varchar(128) not null
);

insert into some_dictionary (some_text, some_array)
values
    ('text2', '{1, 32, 2}'),
    ('text1', '{5, 9, 1}'),
    ('text4', '{1, 97, 4}'),
    ('text3', '{616, 1, 55}'),
    ('text5', '{8, 1}'),
    ('text6', '{1}');

insert into some_dictionary_language (some_dictionary_id, language, name)
values
    (2, 'POLISH', 'nazwa2'),
    (1, 'ENGLISH', 'name1'),
    (3, 'ENGLISH', 'name3'),
    (2, 'ENGLISH', 'name2'),
    (1, 'POLISH', 'nazwa1'),
    (1, 'SPANISH', 'nombre1'),
    (4, 'SPANISH', 'nombre1'),
    (5, 'ENGLISH', '5name'),
    (6, 'ENGLISH', '6name'),
    (6, 'POLISH', 'nazwa5'),
    (5, 'POLISH', 'nazwa6');

给定条件参数：

langugage = 'POLISH' or if not, default = 'ENGLISH'
phrase in some_text or name = 'na'
element in some_array = 1
page = 1 size = 10

我的选择语句没有明显的结果：

select d.id, d.some_text, d.some_array, dl.name, dl.language  
from some_dictionary d 
join some_dictionary_language dl on d.id = dl.some_dictionary_id
where dl."language" in ('POLISH', 'ENGLISH')
and (d.some_text ilike '%na%' or dl.name ilike '%na%')
and 1 = ANY(d.some_array)

选择结果：

d.id    d.some_text d.some_array    dl.name     dl.lanugage
2   text1       {5,9,1}     nazwa2          POLISH
1   text2       {1,32,2}    name1       ENGLISH
3   text4       {1,97,4}    name3       ENGLISH
2   text1       {5,9,1}     name2       ENGLISH
1   text2       {1,32,2}    nazwa1          POLISH
5   text5       {8,1}       5name       ENGLISH
6   text6       {1}     6name       ENGLISH
6   text6       {1}     nazwa5          POLISH
5   text5       {8,1}       nazwa6          POLISH

预期的结果d.id上有不同的语言抛光剂，否则默认英语：

d.id    d.some_text d.some_array    dl.name     dl.lanugage
1   text2       {1,32,2}    nazwa1          POLISH
2   text1       {5,9,1}     name2       POLISH
3   text4       {1,97,4}    name3       ENGLISH (default!)
5   text5       {8,1}       nazwa6          POLISH
6   text6       {1}     6name       POLISH

我尝试做类似的事情：

select distinct on (id) * from (
    select d.id, d.some_text, d.some_array, dl.name, dl.language  
    from some_dictionary d 
    join some_dictionary_language dl on d.id = dl.some_dictionary_id
    where dl."language" in ('POLISH', 'ENGLISH')
    and (d.some_text ilike '%na%' or dl.name ilike '%na%')
    and 1 = ANY(d.some_array)
    order by case when dl."language" = 'POLISH' then 1 end
) sub offset 0 row fetch next 10 rows only;

但是它无法正常工作：

d.id    d.some_text d.some_array    dl.name     dl.lanugage
1   text2       {1,32,2}    nazwa1          POLISH
2   text1       {5,9,1}     name2       ENGLISH
3   text4       {1,97,4}    name3       ENGLISH
5   text5       {8,1}       nazwa6          POLISH
6   text6       {1}     6name       ENGLISH

Answer 1

确定 DISTINCT ON 如何选择其记录的 ORDER BY 应该与 DISTINCT ON 本身处于同一级别，而不是在子查询中。

如果您这样做，错误消息会告诉您问题，您必须首先按 DISTINCT ON 列排序，然后按平局决胜列排序。像这样：

select distinct on (id) d.id, d.some_text, d.some_array, dl.name, dl.language  
from some_dictionary d 
join some_dictionary_language dl on d.id = dl.some_dictionary_id
where dl."language" in ('POLISH', 'ENGLISH')
and (d.some_text ilike '%na%' or dl.name ilike '%na%')
and 1 = ANY(d.some_array)
order by id, case when dl."language" = 'POLISH' then 1 end;

当 DISTINCT ON 查询没有 ORDER BY 时，它只是组成一个仅由 DISTINCT ON 列组成的列，而使每个组中保留的行可以任意选择。