MySQL Case 选择查询

Question

我有一个 users 表，其 marital_status 字段的值为 0-5:
(0-单
1-已婚
2-离异
三寡妇
4-在一段关系中)

我正在尝试对此表运行选择查询并输出 marital_status 字段的相应值而不是数字。

这是我正在使用的查询:

   SELECT user_id,user_first_name, user_last_name,user_email_1,IF(user_gender="0","Male","Female")     AS user_gender,
user_birthdate,
CASE WHEN user_marital_status=0 THEN "Single" END AS user_marital_status, CASE WHEN    user_marital_status = 1 THEN "Married" END AS user_marital_status,
CASE WHEN user_marital_status=2 THEN "Divorced" END AS user_marital_status, CASE WHEN   user_marital_status = 3 THEN "Widowed" END AS user_marital_status,
CASE WHEN user_marital_status = 4 THEN "In a relationship" END  AS user_marital_status
FROM users a

但是，当我运行此查询时，它为 user_marital_status 创建了 5 个不同的列如何让 marital_status 成为具有不同值的一列？

Answer 1

您需要一个带有多个 when 子句的 case 子句:

SELECT user_id,
       user_first_name, 
       user_last_name,
       user_email_1,
       IF(user_gender="0","Male","Female") AS user_gender,
       user_birthdate,
       CASE user_marital_status 
           WHEN 0 THEN 'Single'
           WHEN 1 THEN 'Married'
           WHEN 2 THEN 'Divorced'
           WHEN 3 THEN 'Widowed'
           WHEN 4 THEN 'In a relationship'
       END AS user_marital_status
FROM   users a