代码之家 › 专栏 › 技术社区 › Alex P

SQL-根据最新日期更新记录

tsql sql-server sql

Alex P · 技术社区 · 16 年前

出勤记录表-有3列{员工ID、课程ID、出勤日期、评论}

对于任何给定的课程,员工都会有出勤记录,即如果每年都需要参加该课程,那么他们将有一份与在公司工作年限相同的记录。

我希望能够根据最近的出勤日期更新给定员工和给定课程的“评论”字段。对此,“正确”的SQL语法是什么?

我尝试了很多东西(如下所示),但都没能成功:

UPDATE AttendanceRecordsTable
SET Comments = @Comments
WHERE AttendanceRecordsTable.EmployeeID = (SELECT EmployeeDetailsTable.EmployeeID FROM EmployeeDetailsTable WHERE (EmployeeDetailsTable.LastName =@ParameterLastName AND EmployeeDetailsTable.FirstName =@ParameterFirstName)
AND AttendanceRecordsTable.CourseID = (SELECT CourseDetailsTable.CourseID FROM CourseDetailsTable WHERE CourseDetailsTable.CourseName =@CourseName))
GROUP BY MAX(AttendanceRecordsTable.LastDate)

2 回复 | 直到 16 年前

marc_s MisterSmith 16 年前

所以你想更新 AttendantsRecordsTable ,并将评论设置为最近的评论 CourseDetailsTable 对于每一位员工?

UPDATE 
  dbo.AttendanceRecordsTable
SET 
  Comments = @Comments
FROM
  CourseDetailsTable cd 
INNER JOIN
  Employee e ON e.EmployeeID = AttendanceRecordTable.EmployeeID
WHERE 
  e.LastName = @LastName 
  AND e.FirstName = @FirstName
  AND cd.CourseName = @CourseName
  AND AttendanceRecordsTable.CourseID = cd.CourseID
  AND AttendanceRecordsTable.LastDate = 
        (SELECT MAX(LastDate) 
           FROM AttendanceRecordsTable a
          WHERE a.EmployeeID = e.EmployeeID 
            AND a.CourseID = cd.CourseID)

我认为这样的事情应该奏效。

您基本上需要在要更新的AttendanceRecordTable与Employee和CourseDetailsTable表之间进行联接。对于这两个,您已经定义了某些参数来分别选择一行,然后您需要确保只更新最后一行 AttendanceRecordTable

此处选择子选项:

(SELECT MAX(LastDate) 
   FROM AttendanceRecordsTable a
  WHERE a.EmployeeID = e.EmployeeID AND a.CourseID = cd.CourseID)

将选择最大值(最后一个) LastDate 条目在 AttendanceRecordsTable e.EmployeeID )以及给定的课程( cd.CourseID ).

John Miller 在您的员工表中!).您还可以通过课程名称选择课程,因此课程名称也必须是唯一的,否则您将在课程表中获得多次点击。

cite 16 年前

AttendanceRecordsTable 是 id :

UPDATE AttendanceRecordsTable SET Comments = @Comments
WHERE AttendanceRecordsTable.id = (
    SELECT AttendanceRecordsTable.id
        FROM EmployeeDetailsTable 
        JOIN AttendanceRecordsTable ON AttendanceRecordsTable.EmployeeID = EmployeeDetailsTable.EmployeeIDÂ·
        JOIN CourseDetailsTable ON AttendanceRecordsTable.CourseID = CourseDetailsTable.CourseID
    WHERE
        EmployeeDetailsTable.LastName =@ParameterLastName AND EmployeeDetailsTable.FirstName =@ParameterFirstName AND
        CourseDetailsTable.CourseName =@CourseName
    ORDER BY AttendanceRecordsTable.LastDate DESC LIMIT 1)