Как получить GROUP_CONCAT только для строк с максимальным значением

#mysql #sql #group-by

#mysql #sql #группировка по

Вопрос:

Предположим, у нас есть таблица:

 SELECT * FROM users_to_courses;
 --------- ----------- ------------ --------- 
| user_id | course_id | pass_date  | file_id |
 --------- ----------- ------------ --------- 
|       1 |         1 | 2014-01-01 |       1 |
|       1 |         1 | 2014-01-01 |       2 |
|       1 |         1 | 2014-02-01 |       3 |
|       1 |         1 | 2014-02-01 |       4 |
 --------- ----------- ------------ ---------

Схема:

 CREATE TABLE `users_to_courses` (
  `user_id` int(10) unsigned NOT NULL,
  `course_id` int(10) unsigned NOT NULL,
  `pass_date` date NOT NULL,
  `file_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`user_id`, `course_id`, `pass_date`, `file_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Один пользователь может проходить определенный курс несколько раз, и каждый раз, когда он проходит, может быть сгенерировано несколько сертификатов. user_id и course_id сохраните ссылки на таблицы users и courses. file_id — таблица файлов, в которой хранится информация о файлах сертификатов.

В нашем примере пользователь № 1 дважды прошел курс № 1, и каждый раз было выдано 2 сертификата: всего 4 записи.

Как я могу получить эти данные: для user_id = 1 для каждого курса получите MAX (pass_date) и все файлы, прикрепленные к этой дате. Пока я мог получить только это:

 SELECT
    users_to_courses.course_id,
    MAX(users_to_courses.pass_date) AS max_passed_date,
    GROUP_CONCAT(users_to_courses.file_id SEPARATOR ',') AS files   
FROM
    users_to_courses
WHERE
    users_to_courses.user_id=1
GROUP BY
    users_to_courses.course_id;

 ----------- ----------------- --------- 
| course_id | max_passed_date | files   |
 ----------- ----------------- --------- 
|         1 | 2014-02-01      | 1,2,3,4 |
 ----------- ----------------- ---------

Мне нужно это:

  ----------- ----------------- --------- 
| course_id | max_passed_date | files   |
 ----------- ----------------- --------- 
|         1 | 2014-02-01      |   3,4   |
 ----------- ----------------- ---------

Я думаю, для этого требуется составная GROUP BY .

скрипка

1. Похоже, что ваши данные говорят о том, что он прошел курс 4 раза, дважды в два разных дня. Это допустимый случай? Если это так, вам, вероятно, понадобится как минимум дополнительная таблица, чтобы иметь возможность корректно поддерживать ваши данные…

2. @Clockwork-Muse нет, он прошел дважды и получил 2 сертификата за каждый проход. Как вы думаете, эти данные можно нормализовать?

3. Проблема в том, что на основе только этой таблицы ее интерпретация неоднозначна — любой способ просмотра является правильным, вам нужна метаинформация (например, знание того, что люди не могут проходить один и тот же тест дважды в один и тот же день). Если вы можете проходить тест только один раз в день, все в порядке. Если вы можете использовать его более одного раза, вам нужен какой-то attemptId столбец, что, вероятно, означает новый файл перекрестных ссылок между этим и file_id .

Ответ №1:

Попробуйте следующий запрос, сначала он получает максимальную дату для всех записей, а затем мы можем объединить только эти записи во внешнем запросе. Вы можете использовать один и тот же запрос для нескольких пользователей, добавив group by utc.user_id

 SELECT
    utc.course_id,
    mdt.maxDate AS max_passed_date,
    GROUP_CONCAT(utc.file_id SEPARATOR ',') AS files
FROM
    users_to_courses utc
    join
(SELECT MAX(pass_date) AS maxDate, course_id cId, user_id uId
        FROM users_to_courses GROUP BY user_id, course_id) AS mdt
ON
    mdt.uId = utc.user_id
AND
    mdt.cId = utc.course_id
AND 
    mdt.maxDate = utc.pass_date
WHERE
    utc.user_id=1
GROUP BY
    utc.course_id;

1. Интересно, не эффективнее ли поместить WHERE в подзапрос? И исключение maxdate из GROUP BY немного неприятно.

2. @Strawberry Если вы можете предложить более эффективное решение, пожалуйста, опубликуйте его в качестве другого ответа.