Найти максимальное значение для каждой строки

ProgramBox

Найти максимальное значение для каждой строки

Post author:admin
Запись опубликована:17 апреля, 2023
Post category:Вопросы по программированию

#mysql

Вопрос:

Вот упрощенная версия моего вопроса. Есть две таблицы, в одной из которых указаны компьютеры, а во второй — информация об изменении их состояния со ссылкой на дату (на самом деле не дата, а индекс сдвига, поэтому я должен сделать еще один запрос, чтобы получить саму дату). Я хочу получить последнее состояние каждой машины.
Мой запрос:

 SELECT * FROM machines  
LEFT JOIN (SELECT * FROM statechange, dates WHERE....ORDER BY date DESC LIMIT 0,1) AS state
ON state.mid=machines.mid

Но в результате запроса я получаю только одну машину.
Как я могу это сделать, не создавая представление?

1. Что такое первичный ключ statechange таблицы?

2. Как объединяются statechange и dates? Похоже, вы используете join через предложение where, но вы не упоминаете объединяемые столбцы.

3. на самом деле это не дата, это индекс даты … в таблице date есть несколько полей, включая дату, именно так соединяются statechange и date.

4. @lvil — Значит, в dates таблице есть один (несколько?) столбец внешнего ключа (который не является столбцом даты), который указывает на столбец первичного ключа в таблице statechange?

5. @lvil — Кстати, что-то еще непонятное, date столбец берется из statechange таблицы или из dates table? Если оно исходит из dates таблицы, то решение, которое вы отметили как ответ, не включает это объединение. Если оно исходит из таблицы statechange, зачем вообще нужно соединение с датами?

Ответ №1:

 select m.*,s.`date` from machines as m
inner join (
select mid,`date`
from statechange
order by `date` desc) as s
on m.mid = s.mid
group by m.mid

1. Спасибо. В моем случае это сработало отлично, но как я могу быть уверен, что получу последнее состояние? Или это свойство GROUP BY?

2. предложение group by извлекает первую найденную запись. Поскольку даты указаны в порядке убывания, вы уверены, что получите нужные записи.

3. Разрешено ли вообще предложение Order By в производной таблице, когда вы не используете Limit? Что бы это вообще значило?

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

Ответ №2:

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

 SELECT mid, max(date)
FROM statechange
GROUP BY mid

Затем вы объединяете это, чтобы получить текущее состояние для каждой машины. (Угадывание имен столбцов.)

 SELECT m.*, sc.state, sc.date
FROM machines m
INNER JOIN statechange sc 
    ON ( sc.mid = m.mid )
INNER JOIN (
            SELECT mid, MAX(date) date
            FROM statechange
            GROUP BY mid 
            ) s
    ON ( (s.mid = sc.mid) and (s.date = sc.date) );

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

Ответ №3:

Предполагая, что первичный ключ statechange таблицы — это то, для чего значение Max имеет смысл (например, целое число)

 Select ...
From Machines As M
    Left Join   (
                Select S1.mid, Max( S1.PrimaryKeyCol ) As PrimaryKeyCol
                From statechange As S1
                    Join dates As D1
                        On D1.statechangeFK = S1.PrimaryKeyCol
                Where D1.date = (
                                Select Max( D2.date )
                                From dates As D2
                                Where D2.statechangeFK = D1.statechangeFK
                                )
                Group By S1.mid
                ) As LastStateChange
        On LastStateChange.mid = M.mid

    Left Join statechange As S
        On S.PrimaryKeyCol = LastStateChange.PrimaryKeyCol

Последнее объединение выполняется на случай, если вы хотите вернуть столбцы из таблицы statechange.

Доработка на основе дополнительной информации

 Select ...
From Machines As M
    Left Join   (
                Select S1.mid, Max( S1.PrimaryKeyCol ) As PrimaryKeyCol
                From dates As D1
                    Join statechange As S1
                        On S1.DatesForeignKeyCol = D1.PrimaryKeyCol
                Where D1.date = (
                                Select Max( D2.date )
                                From dates As D2
                                    Join statechange As S2
                                        On S2.DatesForeignKeyCol = D2.PrimaryKeyCol
                                Where S2.mid = S1.mid
                                )
                Group By S1.mid
                ) As LastStateChange
        On LastStateChange.mid = M.mid

    Left Join statechange As S
        On S.PrimaryKeyCol = LastStateChange.PrimaryKeyCol

    Left Join dates As D
        On D.PrimaryKeyCol = S.DatesForeignKeyCol

Одна из причин, по которой я использовал вышеупомянутый подход, заключается в том, чтобы учесть сценарий, в котором у вас может быть несколько statechange строк для одного и того же значения mid и date . Приведенный выше подход будет использовать столбец первичного ключа в качестве связующего звена в этом сценарии.