我试图使用以下方法查询数据库:
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