MySQL中的Case语句

2021-11-20 00:00:00 sql database mysql case

我有一个名为tbl_transaction"的数据库表，其定义如下:

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

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

我想生成两列:Income Amt 和 Expense Amt.

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

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

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?

例如:

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

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

I'm using MySQL as the database. I'm trying to use the CASE statement to accomplish 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;

<小时>

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

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. CASE is 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 considerably slower or less convenient.

相关文章