SQL-запрос для возврата только 1 записи на идентификатор группы
Я ищу способ обработать следующий сценарий. У меня есть таблица базы данных, что я должен возвратить только одну запись для каждого "идентификатора группы", который содержится в таблице, кроме того, запись, которая выбрана в каждой группе, должна быть самым старым человеком в домашнем хозяйстве.
ID Group ID Name Age
1 134 John Bowers 37
2 134 Kerri Bowers 33
3 135 John Bowers 44
4 135 Shannon Bowers 42
Таким образом в демонстрационных данных, обеспеченных выше, мне был бы нужен возвращенный идентификатор 1 и 3, поскольку они - самые старые люди в рамках каждого идентификатора группы.
Это запрашивается против базы данных SQL Server 2005.
4 ответа
SELECT t.*
FROM (
SELECT DISTINCT groupid
FROM mytable
) mo
CROSS APPLY
(
SELECT TOP 1 *
FROM mytable mi
WHERE mi.groupid = mo.groupid
ORDER BY
age DESC
) t
или это:
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY groupid ORDER BY age DESC) rn
FROM mytable
)
WHERE rn = 1
Это вернет максимум одну запись на группу, даже в случае связей.
Смотрите эту статью в моем блоге для сравнения производительности обоих методов:
Использовать:
SELECT DISTINCT
t.groupid,
t.name
FROM TABLE t
JOIN (SELECT t.groupid,
MAX(t.age) 'max_age'
FROM TABLE t
GROUP BY t.groupid) x ON x.groupid = t.groupid
AND x.max_age = t.age
Ну и что, если в группе 2+ человека одного возраста? Лучше сохранить дату рождения, чем возраст - вы всегда можете вычислить дату рождения для презентации.
Попробуйте это (предполагая, что Group] является синонимом Homehold)
Select * From Table t
Where Age = (Select Max(Age)
From Table
Where GroupId = t.GroupId)
Если в каком-то домашнем хозяйстве есть два или более "самых старых" человека (все они одного возраста, и больше никого нет), то это вернет их всех, а не просто одного случайно.
Если это проблема, то вам нужно добавить еще один подзапрос, чтобы вернуть произвольное значение ключа для одного человека в этом наборе.
Select * From Table t
Where Id =
(Select Max(Id) Fom Table
Where GroupId = t.GroupId
And Age =
(Select(Max(Age) From Table
Where GroupId = t.GroupId))
SELECT GroupID, Name, Age
FROM table
INNER JOIN
(
SELECT GroupID, MAX(Age) AS OLDEST
FROM table
) AS OLDESTPEOPLE
ON
table.GroupID = OLDESTPEOPLE.GroupID
AND
table.Age = OLDESTPEOPLE.OLDEST