Как получить ключ от максимальной записи в группе?

У меня есть две таблицы в моей базе данных, одна содержит имена файлов, а другая содержит записи информации, описанной в них, в том числе размеры разделов. он может быть описан как:

Таблица 1: id как целое, имя как varchar

Таблица 2: recid как целочисленный первичный ключ, file_id как integer, оценка как float

Между таблицами существует ссылка "один-ко-многим", от Table1.id до table2.file_id. Мне нужно, чтобы каждый файл, имя которого соответствует определенному шаблону, получает идентификатор связанной записи с максимальным счетом и самой оценкой. До сих пор я использовал:

SELECT name,MAX(score)
FROM Table1
LEFT OUTER JOIN Table2 ON Table2.file_id=Table1.id
WHERE name LIKE :pattern
GROUP BY name

но я не могу получить идентификатор записи в таблице 2 таким образом.

Диалект, который я использую, - это Sqlite.

Какой запрос следует использовать для получения данных о записи, которая имеет максимальный балл для каждого файла?

Обновить:

С этим запросом я приближаюсь к тому, что хочу:

SELECT name,score,recid
FROM Table1
LEFT OUTER JOIN Table2 ON file_id=id 
WHERE name LIKE :pattern
GROUP BY name 
HAVING score=MAX(score)

Однако это не учитывает записи в первой таблице, которые не имеют соответствующих записей во второй таблице. Как я могу обеспечить, чтобы они были в конечном итоге? Должен ли я использовать UNION, и если да - как?

3 ответа

Это может быть достигнуто без GROUP BY, используя блестяще простой метод, описанный @billkarwin здесь:

SELECT name, t2.score
FROM Table1 t1
LEFT OUTER JOIN Table2 t2 ON t2.file_id = t1.id
LEFT OUTER JOIN Table2 t2copy ON t2copy.file_id = t2.file_id
 AND t2.score < t2copy.score
WHERE name LIKE :pattern
 AND t2copy.score IS NULL

См. Демо-версию SQL Fiddle.


Я думаю, что вы должны использовать подзапрос

SELECT name, recid, score
FROM Table1
LEFT OUTER JOIN Table2 ON Table2.file_id=Table1.id
WHERE name LIKE :pattern AND score = (SELECT MAX(score) FROM Table2.score)


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

SELECT name, recid, score
FROM Table1 LEFT OUTER JOIN
 Table2
 ON Table2.file_id=Table1.id
WHERE name LIKE :pattern AND
 score = (SELECT MAX(t2.score)
 FROM Table1 t1 LEFT OUTER JOIN
 Table2 t2 
 ON t2.file_id=t1.id
 where t1.name = table1.name
 );

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

licensed under cc by-sa 3.0 with attribution.