时间戳

首页 > 解决方案 > 如何选择过去 30 天的数据并包含空行

mysql - 如何选择过去 30 天的数据并包含空行

问题描述

我想从数据库（表和）中获取最近 1 个月的出勤率a_days，无论它是否为空。当数据为空时，我想在有显示数据时显示“-” ，并且可以是日期格式。（例如从到）HrEmployeeHrAttLogsFormattedLogsFormatted.DateInWHERE BETWEEN(Y-m-d)2018-06-012018-06-10

询问 ：

SELECT
    Employee.Id as "EmployeeId",
    Employee.Name as "Name",
    TheDay.Days as "Date",
    LogsFormatted.DateIn as "DateIn",
    LogsFormatted.ScanIn as "ScanIn"
FROM
    HrEmployee as Employee
    LEFT JOIN HrEmployeeShift as EmployeeShift ON Employee.ShiftId = EmployeeShift.Id
    LEFT JOIN HrAttMachine as Machine ON Employee.MachineIP = Machine.IP
    LEFT JOIN HrAttLogsFormatted as LogsFormatted ON LogsFormatted.FingerId = Employee.Id
    LEFT JOIN a_days as TheDay ON TheDay.Days = DATE_FORMAT(LogsFormatted.DateIn, "%d")
WHERE Employee.ShiftId = EmployeeShift.Id
AND Employee.Id = '14522228'
ORDER BY Employee.Id, Employee.Name, TheDay.Days, LogsFormatted.DateIn ASC

结果：

FingerId    Name                Date   DateIn        ScanIn
14522228    Aldan Rizki Santosa 3      2018-07-06    06:45:54
14522228    Aldan Rizki Santosa 4      2018-07-09    06:38:12
14522228    Aldan Rizki Santosa 5      2018-07-10    06:48:35

我想要的是：

FingerId    Name                Date   DateIn        ScanIn
14522228    Aldan Rizki Santosa 1      -             -
14522228    Aldan Rizki Santosa 2      -             -
14522228    Aldan Rizki Santosa 3      2018-07-06    06:45:54
14522228    Aldan Rizki Santosa 4      2018-07-09    06:38:12
14522228    Aldan Rizki Santosa 5      2018-07-10    06:48:35
14522228    Aldan Rizki Santosa 6      -             -
.....
..... 
..... Until the date at the end of the month

标签： mysqlsql

解决方案

您可以尝试以下查询并检查吗？

SELECT
     EmployeeDetail.EmployeeId
    ,EmployeeDetail.Name
    ,TheDay.rn AS Days
    ,EmployeeDetail.DateIn
    ,EmployeeDetail.ScanIn
FROM
    (SELECT TOP (DATEDIFF(DAY, '2018-01-01', DATEADD(MONTH,1,'2018-01-01'))) 
     rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
     FROM sys.all_objects AS s1
     CROSS JOIN sys.all_objects AS s2
     ORDER BY s1.[object_id]
    ) TheDay OUTER APPLY
    (SELECT 
     Employee.Id as EmployeeId,
    Employee.Name as Name,
    TheDay.rn as Date,
    LogsFormatted.DateIn as "DateIn",
    LogsFormatted.ScanIn as "ScanIn"
    FROM HrEmployee as Employee
    LEFT JOIN HrEmployeeShift as EmployeeShift ON Employee.ShiftId = EmployeeShift.Id
    LEFT JOIN HrAttMachine as Machine ON Employee.MachineIP = Machine.IP
    LEFT JOIN HrAttLogsFormatted as LogsFormatted ON LogsFormatted.FingerId = Employee.Id
    WHERE LogsFormatted.DateIn = TheDay.rn) AS EmployeeDetail
WHERE EmployeeDetail.EmployeeId = '14522228'
ORDER BY EmployeeDetail.Id, EmployeeDetail.Name, TheDay.Days, EmployeeDetail.DateIn ASC

推荐阅读