在两列之间找到“最大”，并在MySql中显示已为空的某些记录
让我们首先创建一个表-
mysql> create table DemoTable -> ( -> Value1 int, -> Value2 int -> );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable values(78,89); mysql> insert into DemoTable values(19,null); mysql> insert into DemoTable values(null,0); mysql> insert into DemoTable values(null,95);
使用select语句显示表中的所有记录-
mysql> select *from DemoTable;
这将产生以下输出-
+--------+--------+ | Value1 | Value2 | +--------+--------+ | 78 | 89 | | 19 | NULL | | NULL | 0 | | NULL | 95 | +--------+--------+ 4 rows in set (0.00 sec)
这是从两列中找到“最大”的查询-
mysql> select greatest(if(Value1 is null,0,Value1),if(Value2 is null,0,Value2)) from DemoTable;
这将产生以下输出-
+-------------------------------------------------------------------+ | greatest(if(Value1 is null,0,Value1),if(Value2 is null,0,Value2)) | +-------------------------------------------------------------------+ | 89 | | 19 | | 0 | | 95 | +-------------------------------------------------------------------+ 4 rows in set (0.00 sec)