如果您只想跟踪新员工的入职或离职时间,那么应该首先向员工表本身添加相关字段:
HireDate date NOT NULL
和
TerminationDate date NULL
。
然后非常容易在任何特定日期确定人数(和详细信息)。
SELECT EmployeeID, EmployeeName, ...
FROM Employees
WHERE HireDate <= @EndDate
AND (TerminationDate IS NULL OR TerminationDate > @BeginDate)
如果您需要追踪修改(例如标题更改),那么最灵活的方法是使用触发器(或者如果可用,则使用数据库内置的更改跟踪)来维护实时历史记录表。我不建议使用完整的快照,因为这将在应用程序的生命周期内消耗大量空间。
您的历史记录表应包含基本表中的所有字段,再加上两个字段-修改日期和事务类型。可能还有第三个自动编号/序列/标识字段。以下是T-SQL版本:
CREATE TABLE EmployeeHistory
(
TransactionID int NOT NULL IDENTITY(1, 1)
CONSTRAINT PK_EmployeeHistory PRIMARY KEY CLUSTERED,
TransactionDate datetime NOT NULL,
TransactionType tinyint NOT NULL,
EmployeeID int NOT NULL,
EmployeeName varchar(100) NOT NULL,
...
)
然后使用触发器来维护它:
CREATE TRIGGER tr_Employees_History
ON Employees
FOR INSERT, UPDATE
AS BEGIN
INSERT EmployeeHistory (TransactionDate, TransactionType, EmployeeID, ...)
SELECT
GETDATE(),
CASE
WHEN d.EmployeeID IS NULL THEN 1
WHEN (i.TerminationDate IS NOT NULL) AND
(d.TerminationDate IS NULL) THEN 3
ELSE 2
END,
i.EmployeeID, i.EmployeeName, ...
FROM inserted i
LEFT JOIN deleted d
ON d.EmployeeID = i.EmployeeID
END
我假设您不会删除员工记录,而是设置一个“终止日期”;如果您选择删除(请不要这么做),那么您需要编写一个类似的“DELETE”触发器来替换第二个“CASE WHEN i.TerminationDate ...”行。
现在,请填充您的历史记录表:
INSERT EmployeeHistory (TransactionDate, TransactionType, EmployeeID, ...)
SELECT HireDate, 1, EmployeeID, ...
FROM Employees
注意 - 如果您没有
HireDate
,请用
GETDATE()
替换它 - 您的历史记录只能从种子时刻开始有效。
现在,如果您想获取一个历史 "快照",可以执行以下操作:
CREATE FUNCTION dbo.GetEmployeeSnapshot(@ReportDate datetime)
RETURNS TABLE
AS RETURN
WITH History_CTE AS
(
SELECT
TransactionType, EmployeeID, EmployeeName, ...,
ROW_NUMBER() OVER (ORDER BY TransactionDate DESC) AS RowNum
FROM EmployeeHistory
WHERE TransactionDate <= @ReportDate
)
SELECT *
FROM History_CTE
WHERE RowNum = 1
AND TransactionType IN (1, 2)
如果这个查询运行缓慢,如果你需要加速某些聚合操作,比如人数统计,那么你才应该考虑使用快照表:
CREATE TABLE HeadcountHistory
(
ReportDate datetime NOT NULL
CONSTRAINT PK_HeadcountHistory PRIMARY KEY CLUSTERED,
HeadCount int NOT NULL
)
更新过程如下:
CREATE PROCEDURE dbo.UpdateHeadcountHistory
AS
DECLARE @ReportDate datetime
SET @ReportDate = GETDATE()
INSERT HeadcountHistory (HeadCount)
SELECT @ReportDate, COUNT(*)
FROM dbo.GetEmployeeSnapshot(@ReportDate)
将最后一个存储过程作为定期作业运行,然后您将拥有一个针对特定聚合的非规范化报告表。
如果需要更复杂的内容,建议考虑使用数据仓库。