MySQL MySQL中的Case语句

Case statement in MySQL

提问by Pradip Kharbuja

I have a database table called 'tbl_transaction' with the following definition:

我有一个名为“ tbl_transaction”的数据库表，其定义如下：

id INT(11) Primary Key
action_type ENUM('Expense', 'Income')
action_heading VARCHAR (255)
action_amount FLOAT

I would like to generate two columns: Income Amtand Expense Amt.

我想生成两列：Income Amt和Expense Amt。

Is it possible to populate the columns conditionally, using only a SQL Query, such that the output appears in the correct column, depending on whether it is an Expense item or an Income item?

是否可以仅使用 SQL 查询有条件地填充列，以便输出显示在正确的列中，具体取决于它是费用项目还是收入项目？

For example:

例如：

ID        Heading         Income Amt       Expense Amt
1         ABC             1000             -
2         XYZ             -                2000

I'm using MySQL as the database. I'm trying to use the CASE statement to accomplish this.

我使用 MySQL 作为数据库。我正在尝试使用 CASE 语句来完成此操作。

Cheers!

干杯!

回答by cdhowie

Yes, something like this:

是的，像这样：

SELECT
    id,
    action_heading,
    CASE
        WHEN action_type = 'Income' THEN action_amount
        ELSE NULL
    END AS income_amt,
    CASE
        WHEN action_type = 'Expense' THEN action_amount
        ELSE NULL
    END AS expense_amt

FROM tbl_transaction;

---

---

As other answers have pointed out, MySQL also has the IF()function to do this using less verbose syntax. I generally try to avoid this because it is a MySQL-specific extension to SQL that isn't generally supported elsewhere. CASEis standard SQL and is much more portable across different database engines, and I prefer to write portable queries as much as possible, only using engine-specific extensions when the portable alternative is considerablyslower or less convenient.

正如其他答案所指出的那样，MySQL 还具有IF()使用不太冗长的语法来执行此操作的功能。我通常会尽量避免这种情况，因为它是 SQL 的特定于 MySQL 的扩展，其他地方通常不支持。 CASE是标准 SQL 并且在不同的数据库引擎之间具有更高的可移植性，我更喜欢尽可能编写可移植查询，仅在可移植替代方案相当慢或不太方便时才使用特定于引擎的扩展。

回答by Bart Friederichs

MySQL also has IF():

MySQL还有IF()：

SELECT 
  id, action_heading, 
      IF(action_type='Income',action_amount,0) income, 
      IF(action_type='Expense', action_amount, 0) expense
FROM tbl_transaction

回答by Riho

Try to use IF(condition, value1, value2)

尝试使用 IF(condition, value1, value2)

SELECT ID, HEADING, 
IF(action_type='Income',action_amount,0) as Income,
IF(action_type='Expense',action_amount,0) as Expense

回答by Dumitrescu Bogdan

This should work:

这应该有效：

select 
  id
  ,action_heading
  ,case when action_type='Income' then action_amount else 0 end
  ,case when action_type='Expense' then expense_amount else 0 end
from tbl_transaction

回答by nomadkbro

Another thing to keep in mind is there are two different CASEs with MySQL: one like what @cdhowie and others describe here (and documented here: http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#operator_case) and something which is called a CASE, but has completely different syntax and completely different function, documented here: https://dev.mysql.com/doc/refman/5.0/en/case.html

要记住的另一件事是 MySQL 有两种不同的情况：一种类似于 @cdhowie 和其他人在此处描述的内容（并在此处记录：http://dev.mysql.com/doc/refman/5.7/en/control-flow -functions.html#operator_case) 和称为 CASE 的东西，但具有完全不同的语法和完全不同的功能，记录在此处：https: //dev.mysql.com/doc/refman/5.0/en/case.html

Invariably, I first use one when I want the other.

总是，当我想要另一个时，我首先使用一个。

回答by Sushmita Konar

I hope this would provide you with the right solution:

我希望这将为您提供正确的解决方案：

Syntax:

句法：

   CASE  
        WHEN search_condition THEN statement_list  
       [WHEN search_condition THEN statement_list]....
       [ELSE statement_list]  
   END CASE

Implementation:

执行：

select id, action_heading,  
   case when
             action_type="Expense" then action_amount  
             else NULL   
             end as Expense_amt,   
    case when  
             action_type ="Income" then action_amount  
             else NULL  
             end as Income_amt  
  from tbl_transaction;

Here I am using CASEstatement as it is more flexible than if-then-else. It allows more than one branch. And CASEstatement is standard SQL and works in most databases.

在这里我使用CASE语句，因为它比if-then-else. 它允许多个分支。和CASE语句是标准 SQL，适用于大多数数据库。