我有一个表,其中具有针对某些员工数据的“技能ID”及其“能力”。使用IIF,我使用以下查询创建了一个计算字段req_Capacity
SELECT
emp_code,
Skill_ID,
Capacity,
Target_Req,
iif(capacity > target_req,target_req,capacity) as req_Capicity
FROM [load_Style]
ORDER by Skill_ID
现在,我想计算计算出的字段req_capacity以将所有具有相同技能ID的员工分组,如果该技能的总和大于3000,则留下额外的数据。例如我有以下字段
Skill_ID Req_Capacity
1 1800
1 1800
2 1000
2 1000
2 1000
2 1500
```none
**Required Output**
Skill_ID Req_Capacity
1 1800
1 1200
2 1000
2 1000
2 1000
I tried the following query
Select Load_Style.Skill_ID,Load_Style.Capacity
From Load_Style
Where load_Style.Capacity=(Select load_Style.Capacity From load_Style Group by load_style.Skill_ID Having SUM(load_style.capacity)<=3000)
但它给出了一个错误,因为选择列中的列无效,因为该列未包含在聚合函数或GROUP BY子句中。
您需要运行总计的容量才能与3000进行比较SUM OVER
。
select
emp_code,
skill_id,
capacity,
case
when sum(capacity) over(partition by skill_id order by emp_code) - capacity >= 3000 then
0
when sum(capacity) over(partition by skill_id order by emp_code) >= 3000 then
3000 - (sum(capacity) over(partition by skill_id order by emp_code) - capacity)
else
capacity
end as req_capicity
from load_style
order by skill_id, emp_code;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句