MySQL SQL 查询，仅当列不为空时才选择，否则不选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24471704/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query, only select if column is not null else don't select
提问by Anwar
This is what i am trying to do
这就是我想要做的
SELECT `a`, `b`, `c` FROM `tbl` WHERE `a` IS NOT NULL OR `b` IS NOT NULL OR `c` IS NOT NULL WHERE id = ?
If a and c are null and b isn't I still receive this result set\
如果 a 和 c 为空而 b 不是，我仍然收到此结果集\
a b c
____ ____ ____
NULL value NULL
But i only want that it returns this
但我只希望它返回这个
b
____
value
Thanks in advance!
提前致谢！
回答by wvdz
If you want to get a row with two columns when there are two non-null columns, and 1 if there's only one, you have to dynamically create your query.
如果您想在有两个非空列时获得具有两列的行，如果只有一个，则为 1，您必须动态创建查询。
If you want to always have 1 column where each row contains a non-null value, you can do it with a union.
如果您希望始终有 1 列，其中每行包含一个非空值，您可以使用联合来实现。
SELECT a FROM tbl WHERE a IS NOT NULL AND id = ?
UNION
SELECT b FROM tbl WHERE b IS NOT NULL AND id = ?
UNION
SELECT c FROM tbl WHERE c IS NOT NULL AND id = ?
If you want to able to know which from which columns the values come, you can do something like this:
如果您想知道值来自哪些列，您可以执行以下操作：
SELECT 'col a' AS ColName, a FROM tbl WHERE a IS NOT NULL AND id = ?
UNION
SELECT 'col b', b FROM tbl WHERE b IS NOT NULL AND id = ?
UNION
SELECT 'col c', c FROM tbl WHERE c IS NOT NULL AND id = ?
Note: union also removes duplicate results. If you want to keep duplicates, use UNION ALL.
注意： union 还会删除重复的结果。如果要保留重复项，请使用UNION ALL.
