SELECT
u.Name,
u.IntelUserCode,
u.UserCode,
organizationname(6, sbi.Class) AS ClassName,
organizationname(1, sbi.Class) AS AcademyName,
sla.RecordId,
sla.LeaveReason,
sla.LeaveType,
bizname(sla.LeaveType) AS LeaveTypeName,
sla.Evidence,
sla.StartDate,
sla.EndDate,
sla.ClassNum,
sla.OutPlace,
bizname(sla.OutPlace) AS OutPlaceName,
sla.DetailAddress,
sla.EmergencyNumber,
sla.BizType,
sla.TaskCode,
sla.ApproveStatus,
sla.ApplyDate,
sla.LeaveDate,
(SELECT Name
FROM User
WHERE IntelUserCode = c.Counselor) AS CounselorName
FROM User u
JOIN StudentBasicInfo sbi ON sbi.IntelUserCode = u.IntelUserCode AND sbi.Status = u.Status
JOIN StudentLeaveApplication sla ON sla.IntelUserCode = sbi.IntelUserCode
JOIN Class c ON c.ClassCode = sbi.Class
WHERE u.Status = 1 AND u.Type = '2' AND sla.StartDate > '2017-02-01 00:00' AND sla.EndDate < '2017-02-15 00:00';
这种查询方式是无效的!
测试 “<” 是有效的，可是加上 “>” 后就失效了，具体原因待查！
我们来看表里的字段，奇葩的时间类型！
SELECT
u.Name,
u.IntelUserCode,
u.UserCode,
organizationname(6, sbi.Class) AS ClassName,
organizationname(1, sbi.Class) AS AcademyName,
sla.RecordId,
sla.LeaveReason,
sla.LeaveType,
bizname(sla.LeaveType) AS LeaveTypeName,
sla.Evidence,
sla.StartDate,
sla.EndDate,
sla.ClassNum,
sla.OutPlace,
bizname(sla.OutPlace) AS OutPlaceName,
sla.DetailAddress,
sla.EmergencyNumber,
sla.BizType,
sla.TaskCode,
sla.ApproveStatus,
sla.ApplyDate,
sla.LeaveDate,
(SELECT Name
FROM User
WHERE IntelUserCode = c.Counselor) AS CounselorName
FROM User u
JOIN StudentBasicInfo sbi ON sbi.IntelUserCode = u.IntelUserCode AND sbi.Status = u.Status
JOIN StudentLeaveApplication sla ON sla.IntelUserCode = sbi.IntelUserCode
JOIN Class c ON c.ClassCode = sbi.Class
WHERE u.Status = 1 AND u.Type = '2' AND DATEDIFF(substring(sla.StartDate, 1, 10), '2017-02-01') >= 0
AND DATEDIFF(substring(sla.EndDate, 1, 10), '2017-02-15') <= 0
LIMIT 0, 80
这会就可以了。
不过测试下这样也可以:
DATEDIFF(sla.StartDate, '2017-02-01') >= 0 AND
DATEDIFF(sla.EndDate, '2017-02-15') <= 0
