根据MySQL中的条件从具有NULL和其他值的表中获取记录
让我们首先创建一个表-
create table DemoTable ( value1 int, value2 int, value3 int );
使用插入命令在表中插入一些记录-
insert into DemoTable values(20,40,null); insert into DemoTable values(40,40,null); insert into DemoTable values(null,null,null);
使用select语句显示表中的所有记录-
select *from DemoTable;
这将产生以下输出-
+--------+--------+--------+ | value1 | value2 | value3 | +--------+--------+--------+ | 20 | 40 | NULL | | 40 | 40 | NULL | | NULL | NULL | NULL | +--------+--------+--------+ 3 rows in set (0.00 sec
这是基于NULL和基于某些条件的其他值来获取记录的查询-
select *from DemoTable where (value1 is null or value1!=20) and (value2 is null or value2!=20) and (value3 is null or value3!=20);
这将产生以下输出-
+--------+--------+--------+ | value1 | value2 | value3 | +--------+--------+--------+ | 40 | 40 | NULL | | NULL | NULL | NULL | +--------+--------+--------+ 2 rows in set (0.00 sec)