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

有关数据库设计/SQL的建议,用于按时间顺序检索数据

database-design sql-server sql

Alex P · 技术社区 · 14 年前

具体来说,每个员工每年都必须参加培训课程,我的数据库需要记录他们过去参加培训课程的所有日期。

最终用户将使用该软件作为计划工具,帮助他们为员工预订未来的课程日期。当他们选择给定员工时,他们将看到:

(a)上次出席日期
(b)预计未来出勤日期(即最后出勤日期+1个日历年)

根据我的数据库,任何给定的员工可能有多个过去的课程出勤日期:

EmpName          AttandanceDate

Joe Bloggs             1st Jan 2007
Joe Bloggs           4th Jan 2008
Joe Bloggs           3rd Jan 2009
Joe Bloggs           8th Jan 2010

我的问题是,建立数据库的最佳方法是什么,以便检索最新的课程出勤日期?在上面的例子中,最近一次是2010年1月8日。

有没有一种好的方法可以使用SQL按日期排序并选择最大日期?

我的另一个想法是添加一个名为mostrecent的列,并将其设置为真。

EmpName    AttandanceDate         MostRecent
Joe Bloggs  1st Jan 2007           False
Joe Bloggs  4th Jan 2008           False
Joe Bloggs  3rd Jan 2009           False
Joe Bloggs  8th Jan 2010           True

我想知道这是否可以简化SQL,即

SELECT Joe Bloggs WHERE MostRecent = âTRUEâ

此外,当用户更新给定员工的出勤记录(即最新出勤日期)时,我可以使用SQL来:

搜索员工并设置最大值为假
添加一个mostreecent设置为true的新记录?

有人会推荐这两种方法吗?或者你有一个完全不同的方法来解决这个问题?

4 回复 | 直到 14 年前

Harish Shetty 14 年前

要获取最后一个出勤日期,请使用名为max的组函数,即

SELECT MAX(AttandanceDate)  
FROM   course_data
WHERE  employee_name = 'Joe Bloggs'

要获取所有员工的最大出勤日期:

SELECT   employee_name, MAX(AttandanceDate)  
FROM     course_data
GROUP BY employee_name
ORDER BY employee_name

以上查询将不是为没有参加任何课程的员工返回数据。所以您需要执行一个不同的查询。

SELECT   A.employee_name, B.AttandanceDate
FROM     employee AS A
LEFT JOIN (
           SELECT   employee_id, MAX(AttandanceDate) AS AttandanceDate
           FROM     course_data
           GROUP BY employee_id
          ) AS B ON A.id = B.employee_id
ORDER BY A.employee_name

对于没有参加任何课程的员工,查询将返回空值。 AttendanceDate .

Andrew Bezzub 14 年前

该标志是多余的。另一种方法是如何让员工获得最后一次出席日:

select top 1 AttandanceDate
from course_data
WHERE  employee_name = 'Joe Bloggs'
order by AttandanceDate desc

Matt 14 年前

这种情况可能已经发生了,但是来自attandancedate列的输出使我怀疑该列可能不是datetime列。大多数RDBMS都有一些日期、时间和/或日期-时间数据类型用于存储此信息。其中坎达达博古和欧姆格小马的反应是完美的。但是,如果您将日期存储为字符串,则在尝试执行它们的任何建议时都会遇到问题。

使用日期时间数据类型通常还可以使您了解获取日期详细信息的可能性,例如:

例如,select year(2008-01-01)将以整数形式返回2008。

Glen Little 14 年前

如果运行的是SQL Server 2005或2008或更高版本,则可以使用 row_number() 做如下的事情。这将列出每个人,以及他们最近的出席情况。

with temp1 as 
 (select *
      , (row_number() over (partition by EmpName order by AttandanceDate descending)) 
        as [CourseAttendanceOrder]
    from AttendanceHistory)
select *
  from temp
 where CourseAttendanceOrder = 1
 order by EmpName

这可以放在视图中,这样您可以根据需要使用它。

但是,如果您总是一次只关注一个人,那么创建一个可以使用如下语句的存储过程可能会更有效 select max(AttandanceDate) 只为你工作的人。