Sql Server查询选择Top并按分组

SpousesTable SpouseID

SpousePreviousAddressesTable PreviousAddressID, SpouseID, FromDate, AddressTypeID

SpousePreviousAddressesTable PreviousAddressID,SpouseID,FromDate,AddressTypeID

What I have now is updating the most recent for the whole table and assigning the most recent regardless of SpouseID the AddressTypeID = 1

我现在拥有的是更新整个表的最新版本并分配最新的,无论SpouseID是AddressTypeID = 1

I want to assign the most recent SpousePreviousAddress.AddressTypeID = 1 for each unique SpouseID in the SpousePreviousAddresses table.

我想为SpousePreviousAddresses表中的每个唯一SpouseID分配最新的SpousePreviousAddress.AddressTypeID = 1。

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

I think I need a group by but my sql isn't all that hot. Thanks.

我想我需要一个小组,但我的sql并不是那么热门。谢谢。

Update that is Working

正在运行的更新

I was wrong about having found a solution to this earlier. Below is the solution I am going with

我之前找到了解决方案的错误。以下是我要使用的解决方案

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 个解决方案

#1

Presuming you are using SQLServer 2005 (based on the error message you got from the previous attempt) probably the most straightforward way to do this would be to use the ROW_NUMBER() Function couple with a Common Table Expression, I think this might do what you are looking for:

假设您正在使用SQLServer 2005(基于您从上一次尝试中获得的错误消息)可能最直接的方法是使用ROW_NUMBER()函数与Common Table Expression一起使用,我想这可能会对您有所帮助正在找:

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

In SQLServer2005 the ROW_NUMBER() function is one of the most powerful around. It is very usefull in lots of situations. The time spent learning about it will be re-paid many times over.

在SQLServer2005中,ROW_NUMBER()函数是最强大的函数之一。它在很多情况下非常有用。学习它的时间将多次重新支付。

The CTE is used to simplyfy the code abit, as it removes the need for a temporary table of some kind to store the itermediate result.

CTE用于简单地编写代码abit,因为它不需要某种临时表来存储itermediate结果。

The resulting query should be fast and efficient. I know the select in the CTE uses *, which is a bit of overkill as we dont need all the columns, but it may help to show what is happening if anyone want to see what is happening inside the query.

生成的查询应该快速有效。我知道CTE中的select使用*,这有点过分,因为我们不需要所有列,但如果有人想看看查询中发生了什么,它可能有助于显示正在发生的事情。

#2

Here's one way to do it:

这是一种方法:

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;

In other words, update the row spa1 for which no other row spa2 exists with the same spouse and a greater (more recent) date.

换句话说,更新没有其他行spa2存在的行spa1具有相同的配偶和更大(更近)的日期。

There's exactly one row for each value of SpouseID that has the greatest date compared to all other rows (if any) with the same SpouseID.

对于具有相同SpouseID的所有其他行(如果有),具有最大日期的SpouseID的每个值恰好有一行。

There's no need to use a GROUP BY, because there's kind of an implicit grouping done by the join.

没有必要使用GROUP BY,因为有一种由连接完成的隐式分组。

update: I think you misunderstand the purpose of the OUTER JOIN. If there is no row spa2 that matches all the join conditions, then all columns of spa2.* are returned as NULL. That's how outer joins work. So you can search for the cases where spa1 has no matching row spa2 by testing that spa2.SpouseID IS NULL.

更新:我认为你误解了OUTER JOIN的目的。如果没有与所有连接条件匹配的行spa2,则spa2。*的所有列都将返回为NULL。这就是外连接的工作方式。因此,您可以通过测试spa2.SpouseID IS NULL来搜索spa1没有匹配行spa2的情况。

#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)

Just a guess.

只是一个猜测。