问题描述
我有以下选择语句来获取流的下一个计划项目.如果没有匹配的行,我希望它返回一个默认值.这是我正在使用的 SQL:
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 SQL that 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.
这篇关于如果未找到单行,则返回默认值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!