时间戳

首页 > 解决方案 > 显示表格和给定日期范围中不存在的记录

sql - 显示表格和给定日期范围中不存在的记录

问题描述

假设我有两个表 - 学生和学生出勤表。学生表显示有关学生的基本信息：

学生＃ 名 姓
201710 约翰 史密斯
201711 约翰 能源部
201712 安娜 西
201713 克里斯 镝

学生出勤表显示上课学生的 TimeIn 和 TimeOut：

学生＃ 日期 时间输入 暂停
201710 2016-01-06 00:09:00.000 00:15:00.000
201711 2016-01-06 00:09:10.000 00:15:00.000
201712 2016-01-06 00:09:05.000 00:15:00.000
201713 2016-01-06 00:09:00.000 00:15:00.000
201710 2016-01-07 00:09:00.000 00:15:00.000
201711 2016-01-07 00:09:10.000 00:15:00.000
201712 2016-01-07 00:09:05.000 00:15:00.000
201710 2016-01-08 00:09:00.000 00:15:00.000
201712 2016-01-08 00:09:10.000 00:15:00.000
201713 2016-01-08 00:09:05.000 00:15:00.000

我的目标是还包括当天没有日志的学生的学生编号，我将用它来确定他们缺勤。

学生＃ 日期 时间输入 暂停
201710 2016-01-06 00:09:00.000 00:15:00.000
201711 2016-01-06 00:09:10.000 00:15:00.000
201712 2016-01-06 00:09:05.000 00:15:00.000
201713 2016-01-06 00:09:00.000 00:15:00.000
201710 2016-01-07 00:09:00.000 00:15:00.000
201711 2016-01-07 00:09:10.000 00:15:00.000
201712 2016-01-07 00:09:05.000 00:15:00.000
201713 无效的 无效的 无效的
201710 2016-01-08 00:09:00.000 00:15:00.000
201711 无效的 无效的 无效的
201712 2016-01-08 00:09:10.000 00:15:00.000
201713 2016-01-08 00:09:05.000 00:15:00.000

感谢帮助！

标签： sqlsql-serverdatetsqldatetime

解决方案

首先，您需要创建一个日历表来填充所需的日期，然后CROSS JOIN使用该表来填充它Students。

之后，StudentsAttendance将结果集与表左连接。

日历表：

CREATE TABLE #Cal(AttDate DATE)
DECLARE @MinDate DATE = '2016-01-06', @MaxDate DATE = '2016-01-08'

WHILE @MinDate<=@MaxDate
BEGIN
    INSERT INTO #Cal
    SELECT @MinDate

    SET @MinDate = DATEADD(DAY,1,@MinDate)
END

此表包含以下日期。

AttDate
2016-01-06
2016-01-07
2016-01-08

现在执行查询

SELECT D.Student#,D.AttDate,SA.TimeIn, SA.TimeOut
FROM(
    SELECT S.Student#, AttDate
    FROM #Cal C
    CROSS JOIN Student S
    )D
LEFT JOIN Student_Attendance SA ON SA.Student# = D.Student# AND SA.Date = D.AttDate
ORDER BY D.AttDate,D.Student#

试试这个，如果您遇到任何错误，请告诉我们。

SQLFiddle

推荐阅读