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.

"two args" - args to what? A stored procedure? A function in your client code? — Amadan, Aug 21 '15 at 01:51
As Amadan said, with a little more input on your original question, this question should be easily answered. — Nathan, Aug 21 '15 at 01:57
thanks for your comments, the args means the arguments to Mysql via Mybatis. — Boli-CS, Aug 21 '15 at 02:41

score 1 · Accepted Answer · answered Aug 21 '15 at 02:01

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)

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

1 Answers1