MySQL -从多列中选择条件

Question

我想检查两个列的值，并根据这些值创建第三个列。以下是我的看法：

CREATE VIEW `vw_movies` AS
    select 
        `tbl_movies`.`id` AS `id`,
        `tbl_movies`.`vendor_id` AS `vendor_id`,
        `tbl_vendors`.`title` AS `title_vendor`,
        `tbl_movies`.`title` AS `title`,
        `tbl_movies`.`year` AS `year`,
        `tbl_movies`.`synopsis` AS `synopsis`,
        `tbl_movies`.`plot` AS `plot`,
        `tbl_movies`.`director` AS `director`,
        `tbl_movies`.`stars` AS `stars`,
        `tbl_movies`.`language` AS `language`,
        `tbl_movies`.`rating_id` AS `rating_id`,
        `tbl_ratings`.`title` AS `rating`,
        `tbl_movies`.`img_path` AS `img_path`,
        `tbl_movies`.`trailer_path` AS `trailer_path`,
        `tbl_movies`.`file_path` AS `file_path`,
        `tbl_movies`.`is_active` AS `is_active`,
        (case `tbl_movies`.`is_active`
            when 0 then 'No'
            when 1 then 'Yes'
        end) AS `is_active_text`,
        `tbl_movies`.`is_confirmed` AS `is_confirmed`,
        (case `tbl_movies`.`is_confirmed`
            when 0 then 'No'
            when 1 then 'Yes'
        end) AS `is_confirmed_text`,
        `tbl_movies`.`created` AS `created`,
        `tbl_movies`.`modified` AS `modified`
    from
        ((`tbl_movies`
        join `tbl_vendors` ON ((`tbl_movies`.`vendor_id` = `tbl_vendors`.`id`)))
        join `tbl_ratings` ON ((`tbl_movies`.`rating_id` = `tbl_ratings`.`id`)))

我要检查的是is_confirmed和is_confirmed的值：

• 如果is_confirmed == 1& is_confirmed == 1，那么"Live“
• 如果is_confirmed == 0 && is_confirmed == 0，则为“初始值”
• 否则，如果is_confirmed == 1& is_confirmed == 0，则"In-active/Dead Link“

如何在MySQL中实现这样的if-select语句？

Answer 1

您可以这样使用CASE：

SELECT CASE WHEN is_confirmed = 1 AND is_confirmed = 1 THEN "Live"
            WHEN is_confirmed = 0 AND is_confirmed = 0 THEN "Initial"
            WHEN is_confirmed = 1 AND is_confirmed = 0 THEN 
"In-active/Dead Link" 
END;

请查看此处的参考资料：

case

我用了你给这些列起的名字。但是，我认为您应该重命名它们，因为当前的行为是：

is_confirmed =1和is_confirmed =1 ->可能为真

is_confirmed =0和is_confirmed =0 ->可能为真

is_confirmed =1和is_confirmed =0 ->不为真(它是同一列)