正确书写SQL语句方法

with  t_TSTAFFTITLE  (STAFF_ID,ANTIBIO_LEVEL,TITLE_CODE) as 
( 
select a.STAFF_ID,max(a.ANTIBIO_LEVEL) as ANTIBIO_LEVEL ,
max(a.TITLE_CODE) as TITLE_CODE
from NORTHOPI.TSTAFFTITLE  as  a
group by  a.STAFF_ID
)

SELECT STF.STAFF_ID AS Code,  PS.PERSON_NAME  as Name,
integer(CASE WHEN PS.GENDER='0300000001' THEN 1 
WHEN PS.GENDER='0300000002' THEN 2 ELSE 0 END) AS Sex,  PS.ID_NO AS PersonalID,
CAST(PS.BIRTH_DATE AS TIMESTAMP) AS Birthday, 
TITLE.TITLE_CODE AS TitleCode,STF.PROF_CODE AS Business, STF.BP_NO AS Tel,  STF.CELL_PHONE AS Phone,
integer(CASE WHEN   TITLE.ANTIBIO_LEVEL>=0 then  TITLE.ANTIBIO_LEVEL else  0 end) AS AntibioticLevel,  
sub.TEAMID as SubjectCode,sub.TEAMNAME as SubjectName
FROM NORTHOPI.TSTAFF AS STF 
INNER JOIN NORTHOPI.TPERSON AS PS  ON STF.PERSON_ID = PS.PERSON_ID    
LEFT OUTER JOIN t_TSTAFFTITLE AS TITLE      ON STF.STAFF_ID = TITLE.STAFF_ID    
LEFT OUTER JOIN  NORTHOPI.TTEAMDTL taff on STF.STAFF_ID=taff.STAFFID
LEFT OUTER JOIN NORTHOPI.TTEAMM sub on sub.TEAMID=taff.TEAMID   Where  STF.STAFF_ID='000000'   with ur
 

转载于:https://my.oschina.net/hul/blog/2885329