sql - 在2018年4月4日举行的RM狂欢节上，``Oracle Where Case''比``5公里跑步''中的选手平均跑步时间快(Oracle Where Case faster than the average run time by runners in the '5 Km Run' at the RM carnival held on the 4th April 2018)

Question

So currently my query looks like this and displays all the runners who ran in the '5 Km Run' at the RM carnival held on the 8th September.

(因此，目前我的查询看起来像这样，并显示了9月8日举行的RM狂欢节上“ 5公里跑步”中所有跑步者。)

select 
    concat(competitor.compfname,competitor.complname) as fullname ,
    entry.carndate,
    carnival.carnname,
    entry.eventno,
    event.eventypecode,
    eventtype.eventypedesc,
    round((entryfinishtime - entrystarttime) * 24 * 60, 2) as duration_mins

from competitor
    JOIN entry ON competitor.compno = entry.compno
    JOIN carnival ON entry.carndate = carnival.carndate
    JOIN event ON entry.eventno = event.eventno
    JOIN eventtype ON event.eventypecode = eventtype.eventypecode

where 
    event.eventypecode = '5K'
    AND entry.carndate = '08/SEP/2018'

Order by
    entry.carndate,
    fullname;

Which gives me a table of :

(这给了我一张表格：)

FULLNAME      CARNDATE  CARNNAME                        EVENTNO EVE EVENTYPEDESC DURATION_MINS
------------- --------- ------------------------------- ------- --- ------------ -------------
AnnamariaRose 08/SEP/18 RM Spring Series Caulfield 2018 6       5K  5 Km Run     35.23
FanShu        08/SEP/18 RM Spring Series Caulfield 2018 6       5K  5 Km Run     44.73
JaneRyan      08/SEP/18 RM Spring Series Caulfield 2018 6       5K  5 Km Run     18.23
LingShu       08/SEP/18 RM Spring Series Caulfield 2018 6       5K  5 Km Run     45.73
NanShu        08/SEP/18 RM Spring Series Caulfield 2018 6       5K  5 Km Run     42.73
Sam Ryan      08/SEP/18 RM Spring Series Caulfield 2018 6       5K  5 Km Run     26.23
SebastianCoe  08/SEP/18 RM Spring Series Caulfield 2018 6       5K  5 Km Run     30.23

How do i add to the where statement so that the table only shows all the runners who ran in the '5 Km Run' at the RM carnival held on the 8th September 2018 which were faster than the average run time by runners in the '5 Km Run' at the RM carnival held on the 4th April 2018.

(我该如何添加到where语句中，以便该表仅显示所有在2018年9月8日举行的RM狂欢节上以'5 Km Run'跑步的跑步者，这些跑步者的速度快于'5在2018年4月4日举行的RM狂欢节上的Km Run'。)

  ask by Fish translate from so

Answer 1

You can use a correlated subquery for this:

(您可以为此使用相关子查询：)

with ce as (
      select concat(co.compfname, co.complname) as fullname ,
             en.carndate, c.carnname,
             en.eventno, e.eventypecode, et.eventypedesc,
             round((en.entryfinishtime - en.entrystarttime) * 24 * 60, 2) as duration_mins
      from competitor co join
           entry en 
           on co.compno = en.compno join
           carnival c
           on en.carndate = c.carndate join
           event e
           on en.eventno = e.eventno join
           eventtype et
           on e.eventypecode = et.eventypecode
      <your query here with no order by>
     )
select ce.*
from ce
where ce.eventypecode = '5K' and
      ce.carndate = '08/SEP/2018' and
      ce.duration_mins > (select avg(ce2.duration_mins)
                          from ce ce2
                          where ce.eventypecode = '5K' and
                                ce.carndate = '04/APR/2018'
                         );