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 则“直播”
• 否则如果 is_confirmed == 0 && is_confirmed == 0 则“初始”
• else if is_confirmed == 1 && is_confirmed == 0 then “无效/失效链接”

如何在 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;

在此处检查引用:

https://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#operator_case

我使用了您为这些列指定的名称。但是，我认为您应该重命名它们，因为目前的行为是:

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

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

is_confirmed = 1 AND is_confirmed = 0 -> 永远不会为真(它是同一列)