select case mysql,如何在表达式中使用Mysql SELECT CASE

I am trying to use a select case statement on mysql DB.

SELECT

t.name, t.colour,

CASE WHEN (Amount < 0) AS small, CASE WHEN (Amount > 0) AS large FROM t

Error:

......for the right syntax to use near 'AS small, CASE (Amount > 0)

I want my output like so

Name Colour Small Large

item1 red -35

item2 blue 48

etc.

Been trying with no luck. Help please.

解决方案

Here it is:

SELECT

t.name,

t.colour,

CASE WHEN (t.Amount < 0) THEN t.Amount END AS small,

CASE WHEN (t.Amount > 0) THEN t.Amount END AS large

FROM t

Understanding CASE WHEN:

Example #1:

Suppose,

IF n < 0 THEN

'N is negative'

else if n == 0 THEN 'N is Zero'

else 'N is positive'

Let's convert this if-else if chain in MySQL using CASE WHEN expression:

SET @n := -9;

SELECT

CASE WHEN @n <0 THEN 'N is Negative'

WHEN @n =0 THEN 'N is 0'

ELSE 'N is positive' END AS output;

Output:

output

N is Negative

Example #2:

Now we want to convert the following three if statements in MySQL

IF n <0 THEN 'N is Negative'

IF n == 0 THEN 'N is Zero'

IF n > 0 THEN 'N is Positive'

SET @n := 5;

SELECT

CASE WHEN @n <0 THEN 'N is Negative' END AS negativeOutput,

CASE WHEN @n =0 THEN 'N is 0' END AS zeroOutput,

CASE WHEN @n > 0 THEN 'N is positive' END AS positiveOutput;

Output:

Now the output has three columns:

negativeOutput zeroOutput positiveOutput

N is positive