MySql - Агрегатная функция для выбора предпочтительного варианта, второго выбора, третьего выбора, и т.д.?

select message_id, language_id, message
from
(select if(language_id="de",0,1) as choice, m.*
 from messages m where m.language_id in ("de","en")
 order by choice) z
group by message_id

Настройте свои предпочтения с помощью «если» в выбранном для принудительного предпочтительный язык в верхней части набора результатов, поэтому group by выберет его.

Вы также можете сделать это, но ответ выше, вероятно, лучше подходит для того, для чего вы хотите его использовать.

select *
from messages m where m.language_id = "de" or
 (language_id = "en" and not exists (select 1 from messages n
                                  where n.language_id = "de" and
                                    n.message_id = m.message_id))

Продолжение ваших комментариев. Если вам неудобно использовать конкретное поведение MySQL для GROUP BY (без агрегатных функций), вы можете использовать этот более стандартный код:

select *
 from messages m where m.language_id in ("de","en")
  and if(m.language_id="de",0,1) <= (select min(if(n.language_id="de",0,1))
 from messages n where n.message_id = m.message_id)

Этот запрос сделает именно то, что вам нужно:

SELECT * FROM (
    SELECT * FROM messages
    WHERE language_id IN ('en', 'de')
    ORDER BY FIELD(language_id, 'en', 'de') DESC
) m
GROUP BY message_id;

Языки в FIELD (language_id, 'en', 'de') должны быть упорядочены по приоритету: последний один (в данном случае «de») будет иметь более высокий приоритет, затем «en», затем все остальные.

Предложение WHERE здесь является необязательным и требуется только в том случае, если вы не хотите каких-либо результатов в случаях, когда нет перевода ни для «en», ни для «de».

Редактировать: Шон упомянул тот факт, что предложение GROUP BY для неагрегированных столбцов может давать ненадежные результаты. Это может быть правдой, по крайней мере, в Руководстве по MySQL так сказано (хотя на практике всегда используется первая совпадающая строка (?)).

Во всяком случае, есть еще один запрос с той же идеей, но без упомянутой проблемы.

SELECT m1.*
FROM messages AS m1
INNER JOIN (
    SELECT message_id, MAX(FIELD(language_id, 'en', 'de')) AS weight
    FROM messages
    WHERE language_id IN ('en', 'de')
    GROUP BY message_id
) AS m2
USING(message_id)
WHERE FIELD(m1.language_id, 'en', 'de') = m2.weight;

Вот одно из возможных решений:

Сначала я просто настроил ваши таблицы:

DROP TEMPORARY TABLE IF EXISTS messages;
CREATE TEMPORARY TABLE messages (
  message_id INT,
  language_id INT,
  message VARCHAR(64)
);

INSERT INTO messages VALUES
(1, 1, "Hello"),
(1, 2, "Hellode"),
(1, 3, "Hola"),
(2, 1, "Goodbye"),
(2, 3, "Adios");

И добавил новый параметр для языковых предпочтений:

DROP TEMPORARY TABLE IF EXISTS user_language_preference;
CREATE TEMPORARY TABLE user_language_preference (
  user_id INT,
  language_id INT,
  preference INT
);

INSERT INTO user_language_preference VALUES
(1, 1, 10), # know english
(1, 2, 100); # but prefers 'de'

И запросы ..

Привет:

SET @user_id = 1;
SET @message_id = 1;

# Returns 'Hellode', 'Hello'
SELECT
  m.language_id,
  message
FROM messages AS m, user_language_preference AS l
WHERE message_id=@message_id
  AND m.language_id=l.language_id
  AND user_id=@user_id
ORDER BY preference DESC;

До свидания:

SET @message_id = 2;

# Returns 'Goodbye' as 'de' doesn't have a message there
SELECT
  m.language_id,
  message
FROM messages AS m, user_language_preference AS l
WHERE message_id=@message_id
  AND m.language_id=l.language_id
  AND user_id=@user_id
ORDER BY preference DESC;

Edit: В ответ на комментарий:

SELECT
  m.message_id,
  m.language_id,
  message
FROM messages AS m, user_language_preference AS l
WHERE m.language_id=l.language_id
  AND user_id=@user_id
ORDER BY m.message_id, preference DESC;

Используйте уловку group-concat , чтобы получить это в одном запросе:

select message_id,
       substring(max(concat(if(language_id='de', 9, if(language_id='en',8,0)), message)),2) as message,
       substring(max(concat(if(language_id='de', 9, if(language_id='en',8,0)), language_id)),2) as language
from messages 
group by message_id;

просто добавьте условия и соответствующие приоритеты в предложениях IF, чтобы добавить больше резервных языков.

SELECT *
FROM messages
WHERE (message_id,CASE language_id WHEN 'de' THEN 1 WHEN 'en' THEN 2 ELSE NULL END) IN (
    SELECT message_id, MIN(CASE language_id WHEN 'de' THEN 1 WHEN 'en' THEN 2 ELSE NULL END) pref_language_id
    FROM `messages`
    GROUP BY message_id
)

Вы должны изменить CASE language_id WHEN 'de' THEN 1 WHEN 'en' THEN 2 ELSE NULL END на предпочтительный язык (языки) пользователя. Если у него есть третий, просто добавьте еще один случай, например. CASE language_id WHEN 'de' THEN 1 WHEN 'en' THEN 2 WHEN 'es' THEN 3 ELSE NULL END .

Это хороший пример группового максимального запроса. http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

Вот что я придумал с участием. Использование тех же данных и схемы, что и simendsjo.

SELECT prefered.message_id, p2.language_id, message FROM
  (SELECT message_id, MAX(preference) AS prefered FROM messages m
  JOIN user_language_preference p ON p.language_id = m.language_id AND p.user_id = 1
  GROUP BY m.message_id) AS prefered
  JOIN user_language_preference p2 ON prefered = p2.preference AND p2.user_id = 1
  JOIN messages m2 ON p2.language_id = m2.language_id AND m2.message_id = prefered.message_id

Вот как это работает.

1. Внутренний запрос предпочитаемый выбирает все сообщения, объединяет их с языковыми предпочтениями пользователя, вычисляя максимальное предпочтение для каждого сообщения ( GROUP BY m.messsage id ). Если сейчас есть перевод, максимум будет для следующего предпочтительного языка и так далее ...
2. Внешний запрос состоит из двух объединений: первое объединение получает идентификатор языка из максимального предпочтения ( MAX (предпочтение) = предпочтительный = p2.preference ) для данного пользователя.
3. Последнее соединение m2 просто выбирает перевод для известных предпочтительных language_id и message_id.

PS. Не забудьте изменить оба вхождения user_id.

Отредактировано, чтобы добавить некоторые альтернативные решения, соответствующие характеру вопроса. : D
(FWIW: «Второй выбор» был моей первой реализацией)

Первый выбор

Этот вариант должен обеспечивать наилучшую производительность, хотя и немного сложнее.
Что еще более важно, он лучше масштабируется для включения 4-го, 5-го, 6-го и т. Д. Языков.
Для решения требуется временная таблица, определяющая приоритет языков (используйте любую технику, которая лучше всего подходит для mysql).
Суть решения находится в подзапросе «Finder»; Как только он определит наилучший доступный приоритетный язык, можно легко снова присоединиться, чтобы получить фактические сообщения.

declare @prio table (prio_id int, lid varchar(5))
insert into @prio values(1, 'de')
insert into @prio values(2, 'en')
insert into @prio values(3, 'es')

select  m.*
from    (
        select  message_id, MIN(prio_id) prio_id
        from    @messages m
                inner join @Prio p on
                  p.lid = m.language_id
        group by message_id
        ) finder
        inner join @Prio p
          on p.prio_id = finder.prio_id
        inner join @messages m
          on m.message_id = finder.message_id
         and m.language_id = p.lid

Второй вариант

Следующая структура запроса должна быть достаточно простой для понимания.
Каждое объединение добавляет к набору результатов любой идентификатор сообщения, которого еще нет в наборе результатов.
UNION ALL достаточно, потому что каждый последующий запрос гарантирует отсутствие дублирования.
Индекс на (language_id, message_id) должен обеспечивать лучшую производительность (особенно если он кластеризован).

select  message_id, language_id, message
from    messages
where   language_id = 'de'
union all
select  message_id, language_id, message
from    messages
where   language_id = 'en' 
    and message_id not in (select message_id from messages where language_id in ('de'))
union all
select  message_id, language_id, message
from    messages
where   language_id = 'es' 
    and message_id not in (select message_id from messages where language_id in ('de', 'en'))

Третий вариант

Интересный вариант с использованием функции COALESCE.
Однако я не ожидаю, что он будет так хорошо работать с большими объемами данных.

select  *,
        COALESCE(
        (select language_id from @messages where message_id = m.message_id and language_id = 'de'),
        (select language_id from @messages where message_id = m.message_id and language_id = 'en'),
        (select language_id from @messages where message_id = m.message_id and language_id = 'es')
        ) language_id,
        COALESCE(
        (select message from @messages where message_id = m.message_id and language_id = 'de'),
        (select message from @messages where message_id = m.message_id and language_id = 'en'),
        (select message from @messages where message_id = m.message_id and language_id = 'es')
        ) message
from    (
        select  distinct message_id
        from    @messages
        ) m