如何创建视图获取所有员工的月末快照列表？

原始Employees表结构及数据 #

目标视图结构及数据 #

当前实现方式 #

目前通过创建新表，每月月末手动插入数据实现上述展示效果，但希望改用视图（View）完成需求。

原始表创建脚本 #

CREATE TABLE Employees (
    Id INT,
    CreatedOn DATE,
    Name VARCHAR(50)
);

INSERT INTO Employees (Id, CreatedOn, Name)
VALUES
    (1, '2023-04-12', 'Mark'),
    (2, '2023-04-28', 'Luke'),
    (3, '2023-05-15', 'Nina'),
    (4, '2023-05-29', 'Karl'),
    (5, '2023-06-17', 'Kara'),
    (6, '2023-06-30', 'Jack');

解决方案：创建视图实现需求 #

完全可以通过视图实现，核心逻辑是生成需要统计的月末日期，再将每个员工数据与大于等于其入职月份的月末日期关联。以下是不同数据库的实现示例：

MySQL版本 #

CREATE VIEW EmployeeMonthEndStatus AS
WITH MonthEnds AS (
    SELECT LAST_DAY('2023-04-01') AS MonthEnd UNION ALL
    SELECT LAST_DAY('2023-05-01') UNION ALL
    SELECT LAST_DAY('2023-06-01')
)
SELECT 
    e.Id,
    e.CreatedOn,
    e.Name,
    me.MonthEnd AS Month
FROM Employees e
JOIN MonthEnds me ON e.CreatedOn <= me.MonthEnd
ORDER BY me.MonthEnd, e.Id;

SQL Server版本（自动生成有员工入职的月份） #

CREATE VIEW EmployeeMonthEndStatus AS
WITH DistinctMonths AS (
    SELECT DISTINCT DATEFROMPARTS(YEAR(CreatedOn), MONTH(CreatedOn), 1) AS MonthStart
    FROM Employees
),
MonthEnds AS (
    SELECT EOMONTH(MonthStart) AS MonthEnd
    FROM DistinctMonths
)
SELECT 
    e.Id,
    e.CreatedOn,
    e.Name,
    me.MonthEnd AS Month
FROM Employees e
JOIN MonthEnds me ON e.CreatedOn <= me.MonthEnd
ORDER BY me.MonthEnd, e.Id;

说明 #

• 视图会自动同步Employees表的更新，无需手动维护数据
• 若需限定特定月份范围，可修改MonthEnds公共表表达式中的日期筛选条件

内容的提问来源于stack exchange，提问作者qudsif