Sql Server Query Выбор сверху и группировка по
СупругиТаблицаSpouseID
СупругаПредыдущийАдресаТаблицаPreviousAddressID, SpouseID, FromDate, AddressTypeID
Сейчас у меня обновляется самое последнее для всей таблицы и назначается самое последнее, независимо от SpouseID, AddressTypeID = 1
Я хочу назначить самый последний SpousePreviousAddress.AddressTypeID = 1 для каждого уникального SpouseID в таблице SpousePreviousAddresses.
UPDATE spa
SET spa.AddressTypeID = 1
FROM SpousePreviousAddresses AS spa INNER JOIN Spouses ON spa.SpouseID = Spouses.SpouseID,
(SELECT TOP 1 SpousePreviousAddresses.* FROM SpousePreviousAddresses
INNER JOIN Spouses AS s ON SpousePreviousAddresses.SpouseID = s.SpouseID
WHERE SpousePreviousAddresses.CountryID = 181 ORDER BY SpousePreviousAddresses.FromDate DESC) as us
WHERE spa.PreviousAddressID = us.PreviousAddressID
Я думаю, мне нужна группа, но мой sql не такой уж и горячий. Спасибо.
Обновление, которое работает
Я ошибся, когда нашел решение этой проблемы раньше. Ниже приведено решение, с которым я собираюсь
WITH result AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY SpouseID ORDER BY FromDate DESC) AS rowNumber, *
FROM SpousePreviousAddresses
WHERE CountryID = 181
)
UPDATE result
SET AddressTypeID = 1
FROM result WHERE rowNumber = 1
Ответы 3
UPDATE spa SET spa.AddressTypeID = 1
WHERE spa.SpouseID IN (
SELECT DISTINCT s1.SpouseID FROM Spa S1, SpousePreviousAddresses S2
WHERE s1.SpouseID = s2.SpouseID
AND s2.CountryID = 181
AND s1.PreviousAddressId = s2.PreviousAddressId
ORDER BY S2.FromDate DESC)
Просто догадка.
Вот один из способов сделать это:
UPDATE spa1
SET spa1.AddressTypeID = 1
FROM SpousePreviousAddresses AS spa1
LEFT OUTER JOIN SpousePreviousAddresses AS spa2
ON (spa1.SpouseID = spa2.SpouseID AND spa1.FromDate < spa2.FromDate)
WHERE spa1.CountryID = 181 AND spa2.SpouseID IS NULL;
Другими словами, обновите строку spa1, для которой не существует другой строки spa2 с тем же супругом и более поздней (более поздней) датой.
Для каждого значения SpouseID есть ровно одна строка, имеющая наибольшую дату по сравнению со всеми другими строками (если есть) с тем же SpouseID.
Нет необходимости использовать GROUP BY, потому что существует своего рода неявная группировка, выполняемая объединением.
Обновить: Я думаю, вы неправильно понимаете назначение OUTER JOIN. Если нет строки spa2, которая соответствует все условиям соединения, то все столбцы spa2.* возвращаются как NULL. Так работают внешние соединения. Таким образом, вы можете найти случаи, когда spa1 не имеет соответствующей строки spa2, проверив этот spa2.SpouseID IS NULL.
Спасибо, это сработало. Мне нужно было только изменить предложение where на WHERE spa1.CountryID = 181 AND spa2.AddressTypeID = 3; (идентификатор супруга никогда не будет нулевым, и я не упомянул, что все они изначально были обновлены до 3) Спасибо за помощь.
RE: update, глядя на это снова, ваш запрос обновляет ту же строку, что и мой исходный запрос. Моя тоже ошибалась. Пожалуйста, взгляните на мой обновленный вопрос для моего текущего решения.
Предполагая, что вы используете SQLServer 2005 (на основе сообщения об ошибке, которое вы получили в предыдущей попытке), вероятно, наиболее простой способ сделать это - использовать пару функций ROW_NUMBER () с общим табличным выражением, я думаю, что это может сделать то, что вы ищем:
WITH result AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY SpouseID ORDER BY FromDate DESC) as rowNumber,
*
FROM
SpousePreviousAddresses
)
UPDATE SpousePreviousAddresses
SET
AddressTypeID = 2
FROM
SpousePreviousAddresses spa
INNER JOIN result r ON spa.SpouseId = r.SpouseId
WHERE r.rowNumber = 1
AND spa.PreviousAddressID = r.PreviousAddressID
AND spa.CountryID = 181
В SQLServer2005 функция ROW_NUMBER () - одна из самых мощных. Это очень полезно во многих ситуациях. Время, потраченное на его изучение, будет многократно окуплено.
CTE используется для упрощения кода abit, поскольку устраняет необходимость во временной таблице какого-либо вида для хранения промежуточного результата.
Полученный запрос должен быть быстрым и эффективным. Я знаю, что выбор в CTE использует *, что немного избыточно, поскольку нам не нужны все столбцы, но это может помочь показать, что происходит, если кто-то захочет увидеть, что происходит внутри запроса.
Спасибо за вашу помощь, я ценю это. Я обязательно посмотрю на детали row_number. Однако это не помогло. Он обновил только запись с самой последней FromDate. Я буду продолжать смотреть на него, но спасибо.
после повторного просмотра моего решения это больше соответствует моему текущему решению, спасибо за помощь.
спасибо, но я получил эту ошибку при этой попытке. Предложение ORDER BY недопустимо в представлениях, встроенных функциях, производных таблицах, подзапросах и общих табличных выражениях, если также не указаны TOP или FOR XML. Будем продолжать искать и вернемся к нему.