用于检查空值的select语句中的MYSQL Case

zhte4eai 于 2022-12-26 发布在 Mysql

关注(0)|答案(4)|浏览(156)

在MySQL查询中，如果我通过：

case guides.Gud_Publish_Date 
     when null then "Unscheduled" 
     else "Forth Coming Titles" 
  end

则它认为所有值都为空，即使Gud_Publish_Date也有值。完整的SQL语句为

SELECT guides.Gud_Id
    , guides.Gud_Image
    , guides.Gud_SubEditor
    , guides.Gud_Reprint_Status
    , guides.Gud_Publish_Date
    , guides.Gud_Img_Chk
    , guides.Gud_Published
    , guides.Gud_View
    , (
        CASE guides.Gud_Publish_Date
            WHEN NULL
                THEN "Unscheduled"
            ELSE "Forth Coming Titles"
            END
        ) AS Schedules
FROM guides

mysql

来源：https://stackoverflow.com/questions/13339551/mysql-case-in-select-statement-for-checking-null

4条答案

按热度按时间

icnyk63a1#

尝试使用IF

SELECT guides.Gud_Id
    , guides.Gud_Image
    , guides.Gud_SubEditor
    , guides.Gud_Reprint_Status
    , guides.Gud_Publish_Date
    , guides.Gud_Img_Chk
    , guides.Gud_Published
    , guides.Gud_View
    , IF(guides.Gud_Publish_Date IS NULL,'Unscheduled','Forth Coming Titles') 
             AS Schedules
FROM guides

或者如果你真的想要CASE

SELECT guides.Gud_Id
    , guides.Gud_Image
    , guides.Gud_SubEditor
    , guides.Gud_Reprint_Status
    , guides.Gud_Publish_Date
    , guides.Gud_Img_Chk
    , guides.Gud_Published
    , guides.Gud_View
    , (
        CASE 
            WHEN guides.Gud_Publish_Date IS NULL
            THEN 'Unscheduled'
            ELSE 'Forth Coming Titles'
        END
      ) AS Schedules
FROM guides

赞(0）回复(0）举报 2022-12-26

x7rlezfr2#

我发现了这个-几个月前的帖子。按照Rajan的意图使用COALESCE选项，你可以做，

SELECT guides.Gud_Id
    , guides.Gud_Image
    , guides.Gud_SubEditor
    , guides.Gud_Reprint_Status
    , guides.Gud_Publish_Date
    , guides.Gud_Img_Chk
    , guides.Gud_Published
    , guides.Gud_View
    , CASE COALESCE(guides.Gud_Publish_Date, 0)
          WHEN 0 THEN 'Unscheduled'
                 ELSE 'Forth Coming Titles'
          END  AS Schedules
FROM guides

上面的代码假设guides.Gud_Publish_Date不能取值0，我可以这么做，因为它是一个日期，如果不是这样，你可以把0改为另一个不能取值的值;比如你喜欢的浮点数3.1415或者空标识符'null'。

赞(0）回复(0）举报 2022-12-26

szqfcxe23#

SELECT guides.Gud_Id
    , guides.Gud_Image
    , guides.Gud_SubEditor
    , guides.Gud_Reprint_Status
    , guides.Gud_Publish_Date
    , guides.Gud_Img_Chk
    , guides.Gud_Published
    , guides.Gud_View
    , (
        CASE WHEN guides.Gud_Publish_Date IS NULL
            THEN "Unscheduled"
            ELSE "Forth Coming Titles"
            END
        ) AS Schedules
FROM guides

赞(0）回复(0）举报 2022-12-26

mpbci0fu4#

试试这个

SELECT guides.Gud_Id
    , guides.Gud_Image
    , guides.Gud_SubEditor
    , guides.Gud_Reprint_Status
    , guides.Gud_Publish_Date
    , guides.Gud_Img_Chk
    , guides.Gud_Published
    , guides.Gud_View
    , coalesce((
        CASE guides.Gud_Publish_Date
            WHEN NULL
                THEN 'Unscheduled'
            ELSE 'Forth Coming Titles'
            END
        ), 'Unscheduled') AS Schedules
FROM guides

赞(0）回复(0）举报 2022-12-26

我来回答

用于检查空值的select语句中的MYSQL Case

4条答案

相关问题

热门标签

最新问答