Oracle 使用 months_between(ddd.s_date, S.VALID_BEGIN_DATE) 来计算月份的差值。
得出的是带小数的值。
SELECT P.PILOT_ID basicInfoId,
P.CLERK_CODE clerkCode,
P.PILOT_NAME pilotName,
S.RETRAINING_PLACE comId,
U.UNIT_NAME unitName,
S.MONTH1 month1,
S.MONTH2 month2,
S.VALID_BEGIN_DATE validBeginDate,
ddd.s_date,
months_between(ddd.s_date, S.VALID_BEGIN_DATE) AS monthnum,
CASE
WHEN SUBSTR(NVL(S.TECHNICAL_STANDARD, '21'), 0, 1) = '1' THEN
'机长'
ELSE
'副驾驶'
END technicalStandard,
S.NEXT_SCIAMACHY nextSciamachy,
S.NEXT_SCIAMACHY_TEACHER nextSciamachyTeacher,
S.TEACHER_STANDARD teacherStandard,
S.SKILLED_INSPECTOR_STAND_DATE skilledInspectorStandDate,
S.OFFICE_INSPECTOR_STAND_DATE officeInspectorStandDate,
S.VALID_END_DATE validEndDate,
S.LICENCE_END_DATE licenceEndDate,
S.NEXT_TEACHER_NUM nextTeacherNum
--S.MAIN_AC_TYPE_CODE mainAcTypeCode
FROM sim_v_pilot P, dc.v_pilot_licence_standard S, flight.BD_AERO_UNIT U, dc.bd_ac_type a,
(
SELECT max(m.s_date) AS s_date, n.pilot_id
FROM sim_training_sim m LEFT JOIN sim_student_assign n ON m.suite_id = n.suite_id
WHERE m.ac_type_code = 14 GROUP BY n.pilot_id
) ddd
WHERE P.PILOT_ID = S.PILOT_ID
AND U.UNIT_CODE = SUBSTR(P.ORG_CODE, 0, 4)
AND S.MAIN_AC_TYPE_CODE = '14'
and s.main_ac_type_code = a.ac_type_code
AND P.WORK_TYPE = '5201'
AND S.MONTH1 IS NOT NULL
AND S.MONTH2 IS NOT NULL
AND ddd.pilot_id = P.Pilot_Id
AND S.NEXT_SCIAMACHY = 1
AND (to_char(ddd.s_date, 'mm') - S.MONTH1 > 1 OR to_char(ddd.s_date, 'mm') - S.MONTH1 < -1)