如果找不到行,则返回默认值
我有以下选择语句,以获取流的下一个预定项目.如果没有匹配的行,我希望它返回默认值.这是我正在使用的行:
I have the following select statement, to grab the next scheduled item for a stream. If there is no matching row, I want it to return a default value. Here's the line I'm using:
SELECT `file` FROM `show`, `schedule`
WHERE `channel` = 1 AND `start_time` <= UNIX_TIMESTAMP()
AND `start_time` > UNIX_TIMESTAMP()-1800 AND `show`.`id` = `schedule`.`file`
ORDER BY `start_time` DESC LIMIT 1
那应该获取最近计划的项目,但如果它早于查询之前30分钟,则不会.但是,如果用户未安排任何时间,则我需要一个默认值,以使某些内容实际在流中播放.我尝试了以下方法:
That should grab the most recently scheduled item, but not if it's older than 30 minutes before the query. However, if the user doesn't schedule anything, I want a default value, so that something actually plays on the stream. I've tried the following:
SELECT COALESCE(`file`, 'default.webm') FROM `show`, `schedule`...
并且:
SELECT IFNULL(`file`, 'default.webm') FROM `show`, `schedule`
但是,如果未找到任何行,它将始终返回空结果.我该如何返回默认值?
However, it always returns an empty result if no rows are found. How can I return a default value instead?
一种方法
SELECT IFNULL(MIN(`file`), 'default.webm') `file`
FROM `show`, `schedule`
WHERE `channel` = 1 AND `start_time` <= UNIX_TIMESTAMP()
AND `start_time` > UNIX_TIMESTAMP()-1800 AND `show`.`id` = `schedule`.`file`
ORDER BY `start_time` DESC LIMIT 1
由于仅返回一行,因此可以使用聚合函数(在这种情况下为MIN()
),该函数可确保在未选择任何记录的情况下获得NULL
.然后IFNULL()
或COALESCE()
将完成其工作.
Since you return only one row, you can use an aggregate function, in that case MIN()
, that ensures that you'll get NULL
if no records selected. Then IFNULL()
or COALESCE()
will do its job.