在MySQL中的case语句 [英] Case statement in MySQL

问题描述

有一个表' tbl_transaction '与

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

可以使用SQL查询列出费用列中的所有费用金额和Income Amt列中的收入金额等结果：

Is it possible to get result using only SQL Query listing all expense amount in Expense Amt column and income amount in Income Amt column like :

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

我使用MySQL作为数据库。

I'm using MySQL as database. And I'm trying to use CASE statement.

推荐答案

是，像这样：

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的特定于SQL的扩展，通常不支持其他地方。 CASE 是标准SQL，并且在不同的数据库引擎之间更容易移植，我更喜欢尽可能多地编写可移植查询，只有当可移植选项相当较慢或较不方便。

---

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.

这篇关于在MySQL中的case语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！