用于检查空值的 select 语句中的 MYSQL Case
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13339551/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL Case in select statement for checking null
提问by user1023242
in MySQL query if I pass :
在 MySQL 查询中，如果我通过：
case guides.Gud_Publish_Date
when null then "Unscheduled"
else "Forth Coming Titles"
end
then it is considering all is null even the Gud_Publish_Date has value also. the full SQL statement is
那么它正在考虑所有都是空的，即使 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
Can anybody help me? Thanks in advance
有谁能够帮助我？提前致谢
回答by John Woo
try using IF
尝试使用 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
or if you really want CASE
或者如果你真的想要 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
回答by Diego
I found this -a couple of months- old post. Using COALESCEoption as intended by Rajan, you can do,
我发现了这个——几个月前的帖子。使用COALESCERajan 预期的选项，您可以执行以下操作，
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
The code above assumes that guides.Gud_Publish_Datecannot take the value 0, which I can do because it is a date. If that weren't the case you can change 0 for another value that it cannot take; maybe your favorite float like 3.1415or a null identifier 'null'.
上面的代码假设guides.Gud_Publish_Date不能取值 0，我可以这样做，因为它是一个日期。如果不是这种情况，您可以将 0 更改为另一个不能接受的值；也许你最喜欢的 float like3.1415或 null identifier 'null'。
回答by Vardan Gupta
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
回答by Rajan
Try this
尝试这个
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
