我有一个some_dictionary表和一个some_dictionary_language表,其中包含多种语言的数据(以及对some_diction的引用(。
我需要在some_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
确定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列组成一个查询,使每组中保留的行可以任意选择。