Запрос SQL для возврата только 1 записи на группу ID
Я ищу способ справиться со следующим сценарием. У меня есть таблица базы данных, которую мне нужно возвращать только одну запись для каждого "идентификатора группы", которая содержится в таблице, кроме того запись, которая выбрана в каждой группе должна быть самым старым человеком в домашнем хозяйстве.
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.
Ответы
Ответ 1
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
Это приведет к возврату не более одной записи на группу даже в случае связей.
См. эту статью в своем блоге для сравнения производительности обоих методов:
Ответ 2
Использование:
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+ человека с одинаковым возрастом для группы? Было бы лучше сохранить дату рождения, а не возраст - вы всегда можете рассчитать дату рождения для презентации.
Ответ 3
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
Ответ 4
Попробуйте это (предположим, что группа является синонимом для домохозяйства)
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))