SQL查询，仅当列不为null时选择，否则不选择 [英] SQL query, only select if column is not null else don't select

问题描述

这就是我想要做的

 SELECT `a`, `b`, `c` FROM `tbl` WHERE `a` IS NOT NULL OR `b` IS NOT NULL OR `c` IS NOT NULL WHERE id = ?

如果a和c为null而b不是，我仍然会收到此结果集\

If a and c are null and b isn't I still receive this result set\

 a      b     c 
____   ____   ____   

NULL  value   NULL

但是我只希望它返回这个

But i only want that it returns this

   b
  ____

  value

提前谢谢！

推荐答案

如果要在有两个非空列的情况下获得包含两列的行，而在只有两个非空列的情况下要获得1，则必须动态创建查询

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列，其中每行包含一个非null值，则可以使用并集来实现.

If you want to always have 1 column where each row contains a non-null value, you can do it with a union.

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 = ?

注意:联合还删除重复的结果.如果要保留重复项，请使用UNION ALL.

Note: union also removes duplicate results. If you want to keep duplicates, use UNION ALL.

这篇关于SQL查询，仅当列不为null时选择，否则不选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！