[转] sql 中with的用法

with tempDeptName(deptName) as
(
      select
             dept_name as deptName
     from
             bas_dept as dept,tpp_materialmuster as muster
     where
             dept.DEPT_ID = muster.NEEDUNIT
           
     union all
     
     select
             corp_name as deptName
     from
              bas_corp as corp,tpp_materialmuster as muster
     where
             corp.corp_id = muster.NEEDUNIT
           
),
tempProjInfo(projName, projCode) as
(
      select
             etfprojName as projName,
            etfprojCode as projCode
     from
             tbi_etfproj as etf, tpp_materialMuster as muster
     where
             etf.etfprojid = muster.projid
           
     union all
     
     select
             etmprojName as projName,
            etmprojCode as projCode
     from
              tbi_etmproj as etm, tpp_materialMuster as muster
     where
               etm.etmprojId = muster.projid
)

select
                   deptname,
                   projname,
                   projcode
      from     tpp_materialmuster as muster,tempDeptName,tempProjInfo