Rewrite correlated subquery in SELECT into JOIN statement

Question

I have following source table:

CREATE TABLE test
    (`step` varchar(1), `cost_time` int, `rank_no` int)
;

INSERT INTO test
    (`step`, `cost_time`, `rank_no`)
VALUES
    ('a', 10, 1),
    ('b', 20, 2),
    ('c', 30, 3)
;

and query like this:

select 
  main.step,
  main.cost_time,
  main.rank_no,
  (select sum(sub.cost_time)
  from test sub
  where sub.rank_no <= main.rank_no)  as total_time
from
  test main

and the result is expected:

| step | cost_time | rank_no | total_time |
|------|-----------|---------|------------|
|    a |        10 |       1 |         10 |
|    b |        20 |       2 |         30 |
|    c |        30 |       3 |         60 |

is it possible to rewrite this sql using join statement and achieve same result?

Answer 1

The best way to write this query is using a cumulative sum:

select main.step, main.cost_time, main.rank_no,
       sum(cost_time) over (order by rank_no) as total_time
from test main;

You cannot rewrite this just using join. You could rewrite it using join and group by:

select main.step, main.cost_time, main.rank_no,
       sum(sub.cost_time) as total_time
from test main join
     test sub
     on sub.rank_no <= main.rank_no
group by main.step, main.cost_time, main.rank_no;

However, I think the correlated subquery is a better solution.