mysql 返回dataset_如果数据集为空,MySQL将返回默认值

我试图使用以下方法查询数据库：

SELECT coalesce(value, "NA") as value

FROM mytable FORCE INDEX (chrs) FORCE INDEX (sites)

WHERE chrom = 1 AND site = 120

如果我在数据库中有一个条目,这个查询工作正常.但是,如果未找到该条目,则返回空数据集.我想更改查询,以便如果在数据库表中找不到它,它仍将返回某种值(对于“值”)而不是空数据集

解决方法:

你怎么添加一个UNION？

( SELECT coalesce(value, "NA") as value

FROM mytable FORCE INDEX (chrs) FORCE INDEX (sites)

WHERE chrom = 1 AND site = 120

)

UNION ALL

( SELECT 'NA' as value

FROM mytable

WHERE NOT EXISTS

( SELECT coalesce(value, "NA") as value

FROM mytable FORCE INDEX (chrs) FORCE INDEX (sites)

WHERE chrom = 1 AND site = 120

)

)

或者如果你想做一次合并,这样你就可以更容易地更改返回的部分,你可以这样选择它.

SELECT COALESCE(value, "NA") as value

FROM

(

( SELECT value

FROM mytable FORCE INDEX (chrs) FORCE INDEX (sites)

WHERE chrom = 1 AND site = 120

)

UNION ALL

( SELECT NULL as value

FROM mytable

WHERE NOT EXISTS

( SELECT value as value

FROM mytable FORCE INDEX (chrs) FORCE INDEX (sites)

WHERE chrom = 1 AND site = 120

)

)

) t

标签：mysql,select,r,coalesce

来源： https://codeday.me/bug/20190708/1404035.html