如何在表达式中使用Mysql SELECT CASE [英] How to use Mysql SELECT CASE WHEN expression

问题描述

我正在尝试在mysql DB上使用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

错误: ......为在'AS small，CASE(Amount> 0)'附近使用正确的语法

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

我想要这样的输出

Name      Colour     Small     Large
item1     red        -35
item2     blue                 48
etc. 

尝试没有运气.请帮助.

Been trying with no luck. Help please.

推荐答案

在这里是

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

了解CASE WHEN:

Understanding CASE WHEN:

示例1:

假设

IF n < 0 THEN
    'N is negative'
else if n == 0 THEN 'N is Zero'
else 'N is positive'

让我们使用CASE WHEN表达式在MySQL中转换此if-else if链:

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
N is Negative

示例2:

现在我们要在MySQL中转换以下三个if语句

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;

输出:

现在输出有三列:

negativeOutput     zeroOutput     positiveOutput
                                   N is positive

这篇关于如何在表达式中使用Mysql SELECT CASE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！