年龄sql查询年龄工龄司龄下属人员脚本

sql查询年龄工龄司龄下属人员脚本

查年龄(sql server) :select year(getdate())-year(Date_of_Birth)年龄from

PERempms

查年龄(Oracle) :selectto_number(to_char(sysdate, 'yyyy' )-to_char(Date_of_Birth, 'YYYY' ) )年龄from PERempms

Oracle:

年龄:selectto_number(to_char(sysdate, 'yyyy' )-to_char(Date_of_Birth, 'YYYY' ) )年龄from PERempms

工龄:selectto_number(to_char(sysdate, 'yyyy' )-to_char(Service_Start_Date, 'YYYY' ))工龄from PERempms

司龄:selectt o_numb er(to_char(s ysdate, 'yyyy' )-to_char(DATE_JOIN, 'YYYY' ))司龄from PERempms

下属人数:

SELECT A.STAFF_NO,NVL(B.COUNT1, '0' ) AS COUNT 1 FROM PEREMPMS

A

LEFT OUTER JOIN (

SELECT SUPERV ISOR_NAME,COUNT(STAFF_NO) AS COUNT1 FROMP EREMPM S

GROUP BY SUPERVISOR_NAME ) B ON A.STAFF_NO=B.SUPERVISOR_NAMEWHERE (A.TERMINATION_DATE IS NULL OR (A.TERMINATION_DATE ISNOT NULL

A ND

TO_CHAR(A.TERMINATI ON_DATE, 'YYYY-MM-DD' )>TO_CHAR(sy s da t e, 'YYYY-MM-DD' )) )

AND A.STATUS<>' 'ORDER BY A.STAFF_NO