我正在尝试根据下表创建一个SELECT语句,该语句将显示特定的行以及每个活动已完成的总小时数。
Employee Details Activity Details Week Number Hours Worked
1 Clyde 163 Sales and Marketing 39 10
1 Clyde 163 Sales and Marketing 40 8
1 Clyde 163 Sales and Marketing 42 6
1 Clyde 151 Web Design 41 5
1 Clyde 151 Web Design 42 5.5
2 Sally 155 Python Coding 39 10
2 Sally 165 Testing 39 15
2 Sally 155 Python Coding 42 10
2 Sally 165 Testing 40 20
2 Sally 155 Python Coding 41 10
5 Tara 155 Python Coding 39 8
5 Tara 155 Python Coding 40 6
5 Tara 155 Python Coding 41 5
5 Tara 151 Web Design 42 11.5
6 Mike 151 Web Design 39 1
6 Mike 151 Web Design 40 1
6 Mike 151 Web Design 41 1到目前为止,这是我的代码。除了计算每个activity.This代码所花费的小时数之外,一切都很好,它给出了这样的错误:单行子查询返回多行。我知道这段代码有错误,但我认为这是向您展示的最清晰的方式。
SELECT a1.ActId,a2.Description,(SELECT SUM (HrsWorked) from ACTION GROUP BY ActId) AS "Total Hours",a1.HrsWorked * a3.HourlyRate AS "Total Pay"
FROM ACTION a1
INNER JOIN ACTIVITY a2 ON a1.ActId = a2.ActId
INNER JOIN ALLOCATION a3 ON a1.EmpId = a3.EmpId;谢谢
发布于 2015-04-24 11:07:52
我认为你可以使用窗口函数来实现你想要的效果:
select a.*,
sum(hoursworked) over (partition by activity) as totalhours
from action a;https://stackoverflow.com/questions/29838025
复制相似问题