Присоединяйтесь по id или нулю и получите первый результат

Question

Я создал запрос ниже:

select * from store str
 left join(
  select * from schedule sdl 
  where day = 3  
  order by
   case when sdl.store_id is null then (
    case when sdl.strong is true then 0 else 2 end
   ) else 1 end, sdl.schedule_id desc
 ) ovr on (ovr.store_id = str.store_id OR ovr.store_id IS NULL)

Пример данных:

STORE
[store_id] [title]  
 20010      Shoes-Shop
 20330      Candy-Shop

[SCHEDULE]
[schedule_id] [store_id] [day] [strong] [some_other_data]
 1             20330      3     f        10% Discount
 2             NULL       3     t        0% Discount

Что я хочу получить от LEFT JOIN это либо данные для NULL store_id (глобальная запись расписания - влияет на все записи магазина), либо фактические данные для данного store_id.

Присоединение к такому запросу возвращает результаты с правильным порядком, но для совпадений как NULL, так и store_id. Имеет смысл использовать оператор OR в предложении соединения.

Ожидаемые результаты:

[store_id] [title]     [some_other_data]
 20010      Shoes-Shop  0% Discount
 20330      Candy-Shop  0% Discount

Текущие результаты:

[store_id] [title]     [some_other_data]
 20010      Shoes-Shop  0% Discount
 20330      Candy-Shop  0% Discount
 20330      Candy-Shop  10% Discount

Если есть более элегантный подход к этому вопросу, я был бы рад следовать ему.

Answer 1

DISTINCT ON должно работать просто отлично, как только вы получите ORDER BY право. В основном, совпадает с strong = TRUE в schedule иметь приоритет, то совпадает с store_id IS NOT NULL:

SELECT DISTINCT ON (st.store_id)
       st.store_id, st.title, sl.some_other_data
FROM   store          st
LEFT   JOIN  schedule sl ON sl.day = 3
                       AND (sl.store_id = st.store_id OR sl.store_id IS NULL)
ORDER  BY NOT strong, store_id IS NULL;

Это работает, потому что:

• Сортировка нулевых значений после всех остальных, кроме специальных

Основы для DISTINCT ON:

• Выберите первую строку в каждой группе GROUP BY?

Альтернатива с LATERAL присоединиться (Postgres 9.3+):

SELECT *
FROM   store st
LEFT   JOIN  LATERAL (
   SELECT some_other_data
   FROM   schedule
   WHERE  day = 3
   AND   (store_id = st.store_id OR store_id IS NULL)
   ORDER  BY NOT strong
        , store_id IS NULL
   LIMIT  1
   ) sl ON true;

Около LATERAL присоединяется:

• В чем разница между LATERAL и подзапросом в PostgreSQL?

Answer 2

Я думаю, что самый простой способ сделать то, что вы хотите, это использовать distinct on, Вопрос в том, как вы это заказываете:

select distinct on (str.store_id) *
from store str left join
     schedule sdl 
     on (sdl.store_id = str.store_id or sdl.store_id is null) and dl.day = 3  
order by str.store_id,
         (case when sdl.store_id is null then 2 else 1 end)

Это вернет store запишите, если доступно, в противном случае schedule запись, которая имеет значение NULL, Примечание: ваш запрос имеет это понятие strength, но вопрос не объясняет, как его использовать. Это можно легко изменить, чтобы включить несколько уровней приоритетов.