how to use 'select' in 'case when then' in mysql?

Question

my mysql table is like this:

id     value
1       10
2       20
3       30
4       40
5       50

there are two args:start and end, what i want is like this:

when start=end, the result is 0
when start>end, the result is 'sum(value) where id<=start and id>end'
when start<end, the result is 'sum(value) where id>start and id<=end'

how to write the sql to get the result? maybe 'case when then' is a good choice but i do not know how to write.

Answer 1

You can put this logic into a case statement like this:

select (cast when @start = @end then 0
             when @start > @end
             then sum(case when id <= @start and id >= @end then value end)
             when @start < @end
             then sum(case when id > @start and id <= @end then value end)
        end)
from table t;

Your logic is very close to this simpler version:

select sum(case when id > least(@start, @end) and
                     id <= greatest(@start, @end)
                then value else 0
           end)