Обновление SQL самым последним в таблице вместо самого последнего в выбранной записи

Мне нужно обновить записи участников, которые не посещали за последние 14 дней. Есть две таблицы: Members и Attendance. Мне нужно обновить запись участника самой последней «Last_Attend_Date», если Attend_Freq меньше 5 за последние 14 дней.

 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 в таблице посещаемости вместо самой последней от участника, которого мне нужно обновить.

 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

sql max sql-server-2005
person Stan    schedule 09.07.2012    source источник

Ответы (2)


arrow_upward
3
arrow_downward

Проблема в том, что вы не связываете свой подзапрос с внешним запросом. Это помогает использовать разные псевдонимы для всех задействованных таблиц, а соединение с 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
person Damien_The_Unbeliever    schedule 10.07.2012
comment
Спасибо. Это работает отлично, и я кое-чему научился на этом пути. Я ценю его. - person Stan; 10.07.2012

arrow_upward
0
arrow_downward

Используя функцию 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
;
person Andriy M    schedule 10.07.2012