如果数据集为空，MySQL 返回默认值

Question

我正在尝试使用以下方法查询数据库:

SELECT coalesce(value, "NA") as value 
FROM mytable FORCE INDEX (chrs) FORCE INDEX (sites) 
WHERE chrom = 1 AND site = 120

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

Answer 1

你加一个 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
    )
)

或者如果您想进行一次合并，这样您就可以更轻松地更改返回的部分(如果为 null)，您可以像这样从中进行选择。

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

DEMO