显示每个活动的位置和员工总成本 [英] 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屋！