GROUP BY并在MySQL中仅显示非空列值
让我们首先创建一个表-
mysql> create table DemoTable -> ( -> Id varchar(100), -> Message varchar(200) -> );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable values('1','');
mysql> insert into DemoTable values('1','Hi');
mysql> insert into DemoTable values('2','Hello');
mysql> insert into DemoTable values('3','Awesome');
mysql> insert into DemoTable values('3','Good Morning');
mysql> insert into DemoTable values('2',NULL);使用select语句显示表中的所有记录-
mysql> select *from DemoTable;
输出结果
+------+--------------+ | Id | Message | +------+--------------+ | 1 | | | 1 | Hi | | 2 | Hello | | 3 | Awesome | | 3 | Good Morning | | 2 | NULL | +------+--------------+ 6 rows in set (0.00 sec)
以下是用于分组并选择第一个非空列的查询-
mysql> select Id,min(Message) from DemoTable -> where Message IS NOT NULL and length(Message) > 0 -> group by Id;
输出结果
+------+--------------+ | Id | min(Message) | +------+--------------+ | 1 | Hi | | 2 | Hello | | 3 | Awesome | +------+--------------+ 3 rows in set (0.06 sec)