最后创建视图,为这几张表的查询结果,视图:
CREATE ALGORITHM=UNDEFINED DEFINER=root@% SQL SECURITY DEFINER VIEW vrs_emp AS select a.EmpSysID AS EmpSysID,a.EmpNo AS EmpNo,a.EmpName AS EmpName,a.CardFingerNo AS CardFingerNo,a.EmpSexSysID AS EmpSexSysID,a.CardTypeSysID AS CardTypeSysID,a.RateSysID AS RateSysID,a.JobSysID AS JobSysID,a.DepartSysID AS DepartSysID,a.StatusSysID AS StatusSysID,a.EmpEngName AS EmpEngName,a.EmpHireDate AS EmpHireDate,a.EmpPositiveDate AS EmpPositiveDate,a.EmpLeaveDate AS EmpLeaveDate,a.EmpLeaveReason AS EmpLeaveReason,a.EmpStatusID AS EmpStatusID,a.EmpMemo AS EmpMemo,a.CertTypeSysID AS CertTypeSysID,a.EmpCertNo AS EmpCertNo,a.EmpBirthDate AS EmpBirthDate,a.CountrySysID AS CountrySysID,a.NationSysID AS NationSysID,a.EduSysID AS EduSysID,a.EmpGdSchool AS EmpGdSchool,a.PolitySysID AS PolitySysID,a.EmpLanguageAbility AS EmpLanguageAbility,a.EmpMarrySysID AS EmpMarrySysID,a.EmpZipNo AS EmpZipNo,a.EmpPhoneNo AS EmpPhoneNo,a.NativeSysID AS NativeSysID,a.EmpAddress AS EmpAddress,a.EmpEmail AS EmpEmail,a.EmpFamilyInfo AS EmpFamilyInfo,a.EmpBiography AS EmpBiography,a.AllowDelete AS AllowDelete,a.EmpLeaveStatus AS EmpLeaveStatus,a.IsAttend AS IsAttend,a.OtherCardNo AS OtherCardNo,(to_days(now()) - to_days(a.EmpPositiveDate)) AS EmpPositiveDay,floor(((to_days(curdate()) - to_days(a.EmpBirthDate)) / 365)) AS EmpAge,b.DepartID AS DepartID,b.DepartName AS DepartName,c.StatusNo AS StatusNo,c.StatusName AS StatusName,d.RateNo AS RateNo,d.RateName AS RateName,e.PolityNo AS PolityNo,e.PolityName AS PolityName,f.NativeNo AS NativeNo,f.NativeName AS NativeName,g.NationNo AS NationNo,g.NationName AS NationName,h.JobNo AS JobNo,h.JobName AS JobName,i.EduNo AS EduNo,i.EduName AS EduName,j.CountryNo AS CountryNo,j.CountryName AS CountryName,k.CertTypeNo AS CertTypeNo,k.CertTypeName AS CertTypeName,m.EmpSexID AS EmpSexID,m.EmpSexName AS EmpSexName,n.EmpStatusName AS EmpStatusName,o.EmpMarryID AS EmpMarryID,o.EmpMarryName AS EmpMarryName,p.CardPhysicsNo10 AS CardPhysicsNo10,p.CardPhysicsNo8 AS CardPhysicsNo8,p.CardSectorNo AS CardSectorNo,p.CardStatusID AS CardStatusID,p.CardStatusDate AS CardStatusDate,p.CardPWD AS CardPWD,p.CardStartDate AS CardStartDate,p.CardEndDate AS CardEndDate,p.CardUseDate AS CardUseDate,p.CardUseTimes AS CardUseTimes,p.RetirementFlag AS RetirementFlag,p.CardBTMoney AS CardBTMoney,p.FaDate AS FaDate,q.CardStatusName AS CardStatusName,r.EmpPhotoPath AS EmpPhotoPath from ((((((((((((((((rs_emp a join rs_depart b on((b.DepartSysID = a.DepartSysID))) left join rs_classstatus c on((c.StatusSysID = a.StatusSysID))) left join rs_classrate d on((d.RateSysID = a.RateSysID))) left join rs_classpolity e on((e.PolitySysID = a.PolitySysID))) left join rs_classnative f on((f.NativeSysID = a.NativeSysID))) left join rs_classnation g on((g.NationSysID = a.NationSysID))) left join rs_classjob h on((h.JobSysID = a.JobSysID))) left join rs_classeducation i on((i.EduSysID = a.EduSysID))) left join rs_classcountry j on((j.CountrySysID = a.CountrySysID))) left join rs_classcerttype k on((k.CertTypeSysID = a.CertTypeSysID))) left join vrs_empsex m on((m.EmpSexSysID = a.EmpSexSysID))) left join vrs_empstatus n on((n.EmpStatusID = a.EmpStatusID))) left join vrs_empmarrystatus o on((o.EmpMarrySysID = a.EmpMarrySysID))) left join rs_empcard p on((p.EmpSysID = a.EmpSysID))) left join vrs_empcardstatus q on((p.CardStatusID = q.CardStatusID))) left join rs_empphoto r on((a.EmpSysID = r.EmpSysID))) where (a.EmpLeaveStatus <> 1)