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';
这种查询方式是无效的!
测试 “” 后就失效了，具体原因待查！
我们来看表里的字段，奇葩的时间类型！
Paste_Image.png
Paste_Image.png
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
147
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
