**市场部所有人，含兼职和离职**
```language
SELECT person.* FROM T_hr_emporgrelation relation
LEFT OUTER JOIN T_bd_person person ON RELATION.FPERSONID = PERSON.FID
LEFT OUTER JOIN T_org_admin adm ON RELATION.FRELATEADMINID = ADM.FID
LEFT OUTER JOIN T_HR_EmpLaborRelation labor ON RELATION.FLABORRELATIONID = LABOR.FID
LEFT OUTER JOIN T_HR_BDEmployeeType empType ON labor.FLaborRelationStateID = EMPTYPE.FID
where relation.FLEFFDT ={ts '2199-12-31'} and adm.FNAME_l2 = '市场部';
```
**总经理职位上所有人，含兼职和离职**
```language
SELECT person.* FROM T_hr_emporgrelation relation
LEFT OUTER JOIN T_bd_person person ON RELATION.FPERSONID = PERSON.FID
LEFT OUTER JOIN T_org_position position ON RELATION.FPOSITIONID = POSITION.FID
LEFT OUTER JOIN T_HR_EmpLaborRelation labor ON RELATION.FLABORRELATIONID = LABOR.FID
LEFT OUTER JOIN T_HR_BDEmployeeType empType ON labor.FLaborRelationStateID = EMPTYPE.FID
where relation.FLEFFDT ={ts '2199-12-31'} and position.FNAME_l2 = '总经理';
```
**市场部负责人，含兼职和离职**
```language
SELECT person.* FROM T_hr_emporgrelation relation
LEFT OUTER JOIN T_bd_person person ON RELATION.FPERSONID = PERSON.FID
LEFT OUTER JOIN T_org_admin adm ON RELATION.FPOSITIONID = adm.FResponPositionID
LEFT OUTER JOIN T_HR_EmpLaborRelation labor ON RELATION.FLABORRELATIONID = LABOR.FID
LEFT OUTER JOIN T_HR_BDEmployeeType empType ON labor.FLaborRelationStateID = EMPTYPE.FID
where relation.FLEFFDT ={ts '2199-12-31'} and adm.FNAME_l2 = '市场部';
```
**员工张三的上级**
```language
SELECT parent.* FROM T_hr_emporgrelation relation
LEFT OUTER JOIN T_bd_person person ON RELATION.FPERSONID = PERSON.FID
LEFT OUTER JOIN T_org_position position ON RELATION.FPOSITIONID = POSITION.FID
inner JOIN T_hr_emporgrelation parentrelation on position.FPARENTID = parentrelation.FPOSITIONID and parentrelation.FLEFFDT ={ts '2199-12-31'}
LEFT OUTER JOIN T_bd_person parent ON parentrelation.FPERSONID = parent.FID
LEFT OUTER JOIN T_HR_BDEmployeeType empType ON parent.FEmployeeTypeID = EMPTYPE.FID
where relation.FLEFFDT ={ts '2199-12-31'} and empType.FISINCOUNT =1 and person.FNAME_l2 = '张三';
```
