显示每个活动的位置和员工总成本 [英] show location and total staff cost for each event
本文介绍了显示每个活动的位置和员工总成本的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
活动桌
EVENT_ID,LACATION,DURATION
工作人员表
SSN,HOURLY_RATE >
EVENT_STAFF桌子
EVENT_ID,SSN
我在EVENT_STAFF表中有很多关系。
解决方案
选择 ev。 EVENT_ID,ev.LOCATION,SUM(s.HOURLY_RATE)费用
来自 EVENT_STAFF es
left 加入事件ev ev.EVENT_ID = es.EVENT_ID
left join staff on s.ssn = es.SSN
group by ev.EVENT_ID,ev.location
order by ev.EVENT_ID
我希望这应该有用!
SELECT LOCATION,TOTAL_COST
FROM
(
SELECT ES.EVENT_ID,SUM(HOURLY_RATE)TOTAL_COST
FROM EVENT_STAFF ES
INNER < span class =code-keyword> JOIN STAFF S ON ES.SSN = S.SSN
GROUP BY ES.EVENT_ID
)T INNER JOIN EVENT E ON T.EVENT_ID = E.EVENT_ID
EVENT TABLE
EVENT_ID, LACATION,DURATION
STAFF TABLE
SSN,HOURLY_RATE
EVENT_STAFF TABLE
EVENT_ID,SSN
I TO MANY RELATIONSHIP IN EVENT_STAFF TABLE.解决方案select ev.EVENT_ID, ev.LOCATION, SUM(s.HOURLY_RATE) cost from EVENT_STAFF es left join event ev on ev.EVENT_ID = es.EVENT_ID left join staff s on s.ssn = es.SSN group by ev.EVENT_ID , ev.location order by ev.EVENT_ID
I hope this should work!
SELECT LOCATION, TOTAL_COST FROM ( SELECT ES.EVENT_ID, SUM(HOURLY_RATE) TOTAL_COST FROM EVENT_STAFF ES INNER JOIN STAFF S ON ES.SSN = S.SSN GROUP BY ES.EVENT_ID ) T INNER JOIN EVENT E ON T.EVENT_ID = E.EVENT_ID
这篇关于显示每个活动的位置和员工总成本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文