MySQL: проблема с max () и группировкой по - неправильные значения

У меня проблема с оператором SQL: используя это

select a.id as ID, a.dur as DUR, DATE(FROM_UNIXTIME(timestampCol)) as date, 
 a_au.re as RE, a_au.stat as STAT from b_c
  inner join c on b_c.c_id = c.id
  inner join a on c.id = a.c_id
  inner join a_au on a.id = a_au.id
  inner join revi on a_au.rev = revi.rev
  where b_c.b_id = 5

Получаю такой результат:

ID  DUR         date   RE  STAT
-------------------------------
31, 10, '2010-07-14', 2200, 0
31, 10, '2010-07-14', 2205, 0
31, 10, '2010-07-14', 2206, 2
31, 10, '2010-07-14', 2207, 0
31, 10, '2010-07-14', 2210, 2
31, 10, '2010-07-15', 2211, 0
31, 10, '2010-07-14', 2213, 1
32, 10, '2010-07-14', 2203, 0
32, 10, '2010-07-14', 2204, 0
32, 10, '2010-07-14', 2208, 2
32, 10, '2010-07-14', 2209, 0
32, 10, '2010-07-15', 2212, 2

Теперь я хочу получить одну строку результатов для одной комбинации идентификатора и даты. Также я хочу получить эту строку результатов с наивысшим номером RE.

Итак, я пишу свое заявление:

select a.id as ID, a.dur as DUR, DATE(FROM_UNIXTIME(timestampCol)) as date, 
 max(a_au.re) as RE, a_au.stat as STAT from b_c
  inner join c on b_c.c_id = c.id
  inner join a on c.id = a.c_id
  inner join a_au on a.id = a_au.id
  inner join revi on a_au.rev = revi.rev
  where b_c.b_id = 5
  group by ID, date

Теперь я получаю такой результат:

ID  DUR         date   RE  STAT
-------------------------------
31, 10, '2010-07-14', 2213, 0
31, 10, '2010-07-15', 2211, 0
32, 10, '2010-07-14', 2209, 0
32, 10, '2010-07-15', 2212, 2

Кажется, все в порядке, у меня одна строка результатов за день / комбинация идентификаторов и строка с наибольшим номером RE. Но: столбец STAT не имеет правильных значений! Ряд

31, 10, '2010-07-14', 2213, 0

должен иметь статус 1:

31, 10, '2010-07-14', 2213, 1

Значит, в моем заявлении должна быть ошибка. Кажется, что MySQL берет первое найденное значение столбца STAT. Но я хочу иметь соответствующий.

Что я должен делать? Я видел другие темы об этом, например здесь: Выбор всех соответствующих поля с использованием MAX и GROUP BY, но я не могу передать его в свой оператор SQL.

Заранее большое спасибо и С наилучшими пожеланиями.


mysql greatest-n-per-group
person Tim    schedule 14.07.2010    source источник
comment
Добавлен тег greatest-n-per-group. Нажмите на этот тег, чтобы увидеть десятки других сообщений с похожими проблемами.   -  person Bill Karwin    schedule 14.07.2010

Ответы (1)


arrow_upward
1
arrow_downward

Использовать:

SELECT a.id as ID, 
       a.dur as DUR, 
       DATE(FROM_UNIXTIME(timestampCol)) as date, 
       a_au.re as RE, 
       a_au.stat as STAT 
 FROM b_c
 JOIN c on b_c.c_id = c.id
 JOIN a on c.id = a.c_id
 JOIN a_au on a.id = a_au.id
 JOIN revi on a_au.rev = revi.rev
 JOIN ( SELECT a.id as ID, 
               DATE(FROM_UNIXTIME(timestampCol)) as date, 
               MAX(a_au.re) as Max_RE
          FROM b_c
          JOIN c on b_c.c_id = c.id
          JOIN a on c.id = a.c_id
          JOIN a_au on a.id = a_au.id
          JOIN revi on a_au.rev = revi.rev
         WHERE b_c.b_id = 5 
      GROUP BY a.id, DATE(FROM_UNIXTIME(timestampCol))) x ON x.id = a.id
                                                         AND x.date = DATE(FROM_UNIXTIME(timestampCol))
                                                         AND x.max_re = a_au.re
WHERE b_c.b_id = 5

К сожалению, MySQL не поддерживает предложение WITH, которое могло бы упростить чтение.

person OMG Ponies    schedule 14.07.2010
comment
Я использовал ваш оператор SQL (я думаю, что это должно быть x.Max_Re = a_au.re вместо x.max_re = a_au.re). Но теперь я получаю две строки с этими данными: 31, 10, '2010-07-14', 2213, 1 31, 10, '2010-07-14', 2213, 1 Итак, я получаю STAT 1 для ID 31 и REV 2213, но другие строки отсутствуют, а эта строка присутствует дважды. - person Tim; 14.07.2010
comment
без x на x.id ...: 31, 10, '2010-07-14', 2200, 0 31, 10, '2010-07-14', 2205, 0 31, 10, '2010-07-14 ', 2206, 2 31, 10,' 2010-07-14 ', 2207, 0 31, 10,' 2010-07-14 ', 2210, 2 31, 10,' 2010-07-15 ', 2211, 0 31, 10, '2010-07-14', 2213, 1 31, 10, '2010-07-14', 2200, 0 31, 10, '2010-07-14', 2205, 0 31, 10, ' 2010-07-14 ', 2206, 2 31, 10,' 2010-07-14 ', 2207, 0 31, 10,' 2010-07-14 ', 2210, 2 31, 10,' 2010-07-15 ', 2211, 0 31, 10,' 2010-07-14 ', 2213, 1 32, 10,' 2010-07-14 ', 2203, 0 32, 10,' 2010-07-14 ', 2204, 0 32, 10, '2010-07-14', 2208, 2 32, 10, '2010-07-14', 2209, 0 32, 10, '2010-07-15', 2212, 2 - person Tim; 14.07.2010
comment
Добавьте group by 1, 2 в подзапрос x. - person ceteras; 15.07.2010
comment
Также добавьте WHERE b_c.b_id = 5 в конце всего запроса. - person ceteras; 15.07.2010
comment
@Tim: Исправлено @ceteras: Спасибо, не знаю, как я это забыл. - person OMG Ponies; 15.07.2010