Множественный случай и условия
У меня есть две такие таблицы:
и
Первая таблица — это основная таблица для хранения идентификатора моей коллекции книг, а вторая таблица — это сведения о коллекции с «коллекцией» внешнего ключа, связанной с первой таблицей.
Я хотел узнать название и автора каждой книги, поэтому попробовал:
select
collection_id,
(case when detail_type = 'title' then detail_value end) as title,
(case when detail_type = 'author' then detail_value end) as author
from
collections
left join
collection_details on collections.collection_id = collection_details.collection
where
detail_type = 'title' or detail_type = 'author';
Результат:
На самом деле я ожидал:
Как я могу получить желаемый результат только с помощью одного запроса?
Ответы 2
Одним из вариантов может быть использование предложения FILTER.
SELECT c.collection_id,
MAX(cd.detail_value) FILTER (WHERE cd.detail_type = 'title') AS title,
MAX(cd.detail_value) FILTER (WHERE cd.detail_type = 'author') AS author
FROM collections c
LEFT JOIN collection_details cd ON c.collection_id = cd.collection
GROUP BY c.collection_id
ORDER BY c.collection_id asc;
В качестве альтернативы вы можете использовать подзапрос, чтобы получить автора и название книги.
SELECT collection_id, book_author,book_title
FROM collections c
LEFT JOIN (SELECT collection,
MAX(CASE WHEN detail_type = 'author' THEN detail_value END) AS book_author ,
MAX(CASE WHEN detail_type = 'title' THEN detail_value END) AS book_title
FROM collection_details
GROUP BY collection
) cd on cd.collection=c.collection_id
ORDER BY collection_id asc;
Я только что понял, что то, что я опубликовал, во многом совпадает с первым примером здесь, только без группировки + агрегирования + фильтрации без видимой причины. filter ничего не делает, поскольку строки были предварительно отфильтрованы по идентичному условию соединения — я думаю, @Isolated разыграл вас всей этой идеей. Если бы у одной коллекции могло быть несколько авторов/названий, вы бы, скорее всего, array_agg() указали здесь, а не только одного.
Я говорил об этом выше: «Если бы одна коллекция могла иметь несколько авторов/названий, вы, скорее всего, использовали бы здесь array_agg(), а не показывали только одного». filter здесь ничего не делает, потому что вы уже указали AND cdTitle.detail_type = 'title' при его вставке, поэтому выполнение FILTER (WHERE cdTitle.detail_type = 'title') не имеет никакого эффекта — к этому моменту все не-заголовки уже исчезли из cdTitle. См. dbfiddle.uk/tm6F_CT7
ПРИСОЕДИНЯЙТЕСЬ к collection_details дважды: демо на db<>fiddle
select c.collection_id
,t.detail_value as title
,a.detail_value as author
from collections as c
left join collection_details as t
on c.collection_id = t.collection and t.detail_type='title'
left join collection_details as a
on c.collection_id = a.collection and a.detail_type='author';
Используйте разные псевдонимы и укажите нужный detail_type в качестве дополнительного join условия.
Если вы разрешите несколько авторов и названий в каждой коллекции, возможно, вы захотите получить string_agg()
select c.collection_id
,string_agg(distinct t.detail_value,', ') as titles
,string_agg(distinct a.detail_value,', ') as authors
from collections as c
left join collection_details as t
on c.collection_id = t.collection and t.detail_type='title'
left join collection_details as a
on c.collection_id = a.collection and a.detail_type='author'
group by 1;
Использование
conditional aggregationпутем переноса в max() — один из вариантов. Я считаю, что у postgres тоже может быть опцияfilter.