SQL更新表中的最新信息，而不是所选记录中的最新信息

Question

我需要更新过去14天内没有参加的会员记录。有两个表:成员和出席率。如果Attend_Freq在过去14天内小于5，我需要用最新的"Last_Attend_Date“更新成员记录。

 Members table:
 ID     Attend_Freq     Last_Attend_Date
123        4              2012-7-5  -- This is the result of the query, the most 
                                       recent date in the table instead of the 
                                       most recent for the member

 Attendance
 ID     Member_ID       Last_Attend_Date
 987      123               2012-6-5
 888      123               2012-6-4
 567      123               2012-6-3
 456      234               2012-6-30
1909      292               2012-7-5

这是一个查询，但它给了我出席表中的最新Last_Attend_Date，而不是我需要更新的成员的最新the。

 UPDATE M
 SET Last_Attend_Date = 
 (SELECT Max(Last_Attend_Date) FROM Attendance A 
 JOIN Members M ON A.Member_ID = M.ID 
 WHERE A.Member_ID =M.id )
 FROM Members M
 JOIN Attendance A on A.Member_ID = M.id
 WHERE Attend_Freq <'5' and Last_Attend_Date <getdate()-14 and A.Member_ID = M.ID

Answer 1

问题是您没有将您的子查询与外部查询关联起来。对所有涉及的表使用不同的别名很有帮助，而且在子查询中连接到Members似乎是不必要的：

create table Members (ID int not null,Attend_Freq int not null,Last_Attend_Date datetime not null)
insert into Members (ID,Attend_Freq,Last_Attend_Date) values
(123,4,'19000101')

create table Attendance (ID int not null,Member_ID int not null,Last_Attend_Date datetime not null)
insert into Attendance (ID,Member_ID,Last_Attend_Date) values
(987,123,'20120605'),
(888,123,'20120604'),
(567,123,'20120603'),
(456,234,'20120630'),
(1909,292,'20120705')

update M
set
    Last_Attend_Date =
        (select MAX(Last_Attend_Date)
            from Attendance A2
        where A2.Member_ID = M.ID) --M is a reference to the outer table here
from
    Members M
        inner join
    Attendance A
        on
            M.ID = A.Member_ID
where
    m.Attend_Freq < 5 and
    A.Last_Attend_Date < DATEADD(day,-14,CURRENT_TIMESTAMP)

select * from Members

结果：

ID          Attend_Freq Last_Attend_Date
----------- ----------- ----------------
123         4           2012-06-05

Answer 2

使用ROW_NUMBER()函数，您可以对每个成员的所有出勤记录进行排名，从最新记录开始，然后，对于每个成员，如果是两周前的一天，则获得最新(即排名靠前的)记录。使用结果(例如，在连接的帮助下)，您现在可以更新Members表，另外还可以检查Attend_Freq < 5

WITH AttendanceRanked AS (
  /* rank the rows per attendance date (in descending order) */
  SELECT
    *,
    rnk = ROW_NUMBER() OVER (PARTITION BY Member_ID
                                 ORDER BY Last_Attend_Date DESC)
  FROM Attendance
),
LastAttendance AS (
  /* get the latest attendance records per member */
  SELECT *
  FROM AttendanceRanked
  WHERE rnk = 1
    AND Last_Attend_Date <= DATEADD(DAY, -14, GETDATE())
)
/* join the latest attendance records with Members and
   update those members that match the additional condition */
UPDATE m
SET Last_Attend_Date = a.Last_Attend_Date
FROM Members m
  INNER JOIN LastAttendance a ON m.Member_ID = a.Member_ID
WHERE m.Attend_Freq < 5
;