April 28, 2024

How to use between in case clause in mysql

I have like following table.

type  score
A     1
A    10
A    90
A   100  
A   101
B     1
B    10
B   101
B   200
B   201
C     1
C    10
C   101
C   200
C   201
 ・
 ・

I’d like to extract score by range. if type in A then 1~100 and if type other than A then 100~200

type score
A    1
A   10 
A   90
A  100
B  101
B  200
C  101
C  200
 ・
 ・

I tried like following query, but it didn’t workwell.

select *
from table
where case when type in ('A') then between 1 and 100 else 100 and 200 

I’d like to avoid union clause because it execute sequential scan manytimes and the code becomes long

If someone has opinion or materials please let me know Thanks