职业指导服务-视图年龄修复.sql 944 B

1234567891011121314151617181920212223242526
  1. -- ============================================================
  2. -- 就业一湛通服务平台 - 职业指导服务 视图年龄修复
  3. -- 数据库:达梦数据库 (DM8)
  4. -- 创建日期:2026-06-12
  5. -- 修复:年龄计算 YEARS_BETWEEN → FLOOR(MONTHS_BETWEEN/12)
  6. -- 原因:YEARS_BETWEEN 可能返回负数或不准确,导致列表年龄显示为负
  7. -- ============================================================
  8. CREATE OR REPLACE VIEW V_CAREER_GUIDANCE_SERVICE AS
  9. SELECT
  10. cgs.ID,
  11. cgs.PERSONAL_ID,
  12. cgs.APPLICATION_REASON,
  13. cgs.APPLICATION_DATE,
  14. cgs.IS_FOLLOWED_UP,
  15. cgs.LAST_NOTIFY_TIME,
  16. pi.FULL_NAME AS NAME,
  17. pi.ID_NUMBER AS ID_CARD,
  18. pi.GENDER,
  19. FLOOR(MONTHS_BETWEEN(SYSDATE, pi.BIRTH_DATE) / 12) AS AGE,
  20. pi.EDUCATION,
  21. pi.HOUSEHOLD_LOCATION,
  22. pi.CONTACT_PHONE,
  23. cgs.CREATE_TIME
  24. FROM CAREER_GUIDANCE_SERVICE cgs
  25. LEFT JOIN ZJRS_JEECG_BOOT.PERSONAL_INFO pi ON cgs.PERSONAL_ID = pi.ID;