# 分析下这个问题

0

SELECT
UU.JName                    as '职务',
SUM(UU.Salary)              as '薪酬总额',
count(UU.Pid)                as '平均人数',
SUM(UU.Salary)/count(UU.Pid) as '人均收入'
FROM
(SELECT
TT.Psid                    as Pid,
JOB.FName_L2              as JName,
TT.Sal                    as Salary
FROM
(SELECT
SCH.FPersonID              as Psid,
PON.FJobID                as JobID,
SCH.FFSAL251              as Sal

FROM  T_HR_SCHM_31247124924456              AS SCH
LEFT  OUTER JOIN  T_BD_Person                AS PS  ON  SCH.FPersonID = PS.FID
LEFT  OUTER JOIN T_HR_PersonPosition        AS PPS  ON  PS.FID = PPS.FPersonID
LEFT  OUTER JOIN T_ORG_Position              AS PON  ON  PPS.FPrimaryPositionID = PON.FID
)AS TT
RIGHT OUTER JOIN T_ORG_JOB                  AS JOB  ON  TT.JobID = JOB.FID) AS UU
GROUP BY UU.JName

select FHorTableName from T_HR_CMPSCHEME where FName_L2 like '在职员工薪酬方案%' （其中where后面不固定,不同的方案对应的表名不同）

sunway0628 | 初学一级 | 园豆：60

0

declare @strsql  varchar(max),@strwhere varchar(200)

set @strwhere = '在职员工薪酬方案%'
set @strsql = '
SELECT
UU.JName                    as ''职务'',
SUM(UU.Salary)              as ''薪酬总额'',
count(UU.Pid)                as ''平均人数'',
SUM(UU.Salary)/count(UU.Pid) as ''人均收入''
FROM
(SELECT
TT.Psid                    as Pid,
JOB.FName_L2              as JName,
TT.Sal                    as Salary
FROM
(SELECT
SCH.FPersonID              as Psid,
PON.FJobID                as JobID,
SCH.FFSAL251              as Sal

FROM  (select FHorTableName from T_HR_CMPSCHEME where FName_L2 like ''' + @strwhere + ''')              AS SCH
LEFT  OUTER JOIN  T_BD_Person                AS PS  ON  SCH.FPersonID = PS.FID
LEFT  OUTER JOIN T_HR_PersonPosition        AS PPS  ON  PS.FID = PPS.FPersonID
LEFT  OUTER JOIN T_ORG_Position              AS PON  ON  PPS.FPrimaryPositionID = PON.FID
)AS TT
RIGHT OUTER JOIN T_ORG_JOB                  AS JOB  ON  TT.JobID = JOB.FID) AS UU
GROUP BY UU.JName'

langford | 园豆：710 (小虾三级) | 2010-03-06 21:54
0

0

SELECT
UU.JName                    as '职务',
SUM(UU.Salary)              as '薪酬总额',
count(UU.Pid)                as '平均人数',
SUM(UU.Salary)/count(UU.Pid) as '人均收入'
FROM
(SELECT
TT.Psid                    as Pid,
JOB.FName_L2              as JName,
TT.Sal                    as Salary
FROM
(SELECT
SCH.FPersonID              as Psid,
PON.FJobID                as JobID,
SCH.FFSAL251              as Sal

FROM  #T_HR_SCHM_Temp  AS SCH
LEFT  OUTER JOIN  T_BD_Person                AS PS  ON  SCH.FPersonID = PS.FID
LEFT  OUTER JOIN T_HR_PersonPosition        AS PPS  ON  PS.FID = PPS.FPersonID
LEFT  OUTER JOIN T_ORG_Position              AS PON  ON  PPS.FPrimaryPositionID = PON.FID
)AS TT
RIGHT OUTER JOIN T_ORG_JOB                  AS JOB  ON  TT.JobID = JOB.FID) AS UU
GROUP BY UU.JName

您需要登录以后才能回答，未注册用户请先注册