我想从数据库（表）中获取最近1个月的出勤率 a_days , HrEmployee 以及 HrAttLogsFormatted )当它是空的或不是。当数据为空时，当有显示数据时，我想显示“-” LogsFormatted.DateIn ，它可以在 WHERE BETWEEN 与 (Y-m-d) 日期格式(示例来自 2018-06-01 至 2018-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
1条答案
按热度按时间rn0zuynd1#
你能试试下面的查询和检查吗？
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