How to use between in case clause in mysql-CodePudding

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

CodePudding user response：

You're just missing score for your BETWEEN:

SELECT *
 FROM mytable
 WHERE CASE WHEN TYPE IN ('A') THEN score BETWEEN 1 AND 100 
                           ELSE score BETWEEN 100 AND 200 END;

If you have other condition, you can always add another WHEN like the following:

SELECT *
 FROM mytable
 WHERE CASE WHEN type IN ('A') THEN score BETWEEN 1 AND 100 
            WHEN type IN ('B','C') THEN score BETWEEN 100 AND 200 ELSE 0 END

Demo fiddle

CodePudding user response：

select *
from tb
where ([type] = 'A' and score between 1 and 100) or ([type] <> 'A' and score between 101 and 200)

Demo in db<>fiddle