编写了如下脚本,统计各种职务的收入情况: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上面脚本中表名T_HR_SCHM_31247124924456,实际中必须是动态的表名,表名由以下语句得到
select FHorTableName from T_HR_CMPSCHEME where FName_L2 like '在职员工薪酬方案%' (其中where后面不固定,不同的方案对应的表名不同)请问怎么写出满足要求的语句?
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上面脚本中表名T_HR_SCHM_31247124924456,实际中必须是动态的表名,表名由以下语句得到
select FHorTableName from T_HR_CMPSCHEME where FName_L2 like '在职员工薪酬方案%' (其中where后面不固定,不同的方案对应的表名不同)请问怎么写出满足要求的语句?
解决方案 »
- 请教如何建立一个三级分类数据库的问题,很急,100分+请吃饭
- 数据库新手提问。
- 表遍历问题,昨天问过解决不了,再请教大家。
- 难! 怎样得到SQL返回结果的第N行?
- 鄒健火速救命!!!!!在線等。客戶端(delphi寫的界面)連到數據庫時,有些表無法打開,有些表又能打開,是什麼原因?
- 求个SQL语句
- Virtual pc 2007上安装SQLServer2005 express失败,请教原因!!
- 高分求锁例
- 请问学delphi应该看什么书啊(新手)还有数据库要看什么书啊
- 找优秀的数据库开发人员
- vs2003下使用_RecordSetPtr出现的一个问题
- select count(*) from tb是否需要扫描全部的索引中间层?
传入表名参数生成SQL语句
EXEC一下
set @t=''
select @t=@t+FHorTableName +''',''' from T_HR_CMPSCHEME where FName_L2 like '在职员工薪酬方案%' select ' select '''+left(@t,len(@t)-2)+' from tablename '