时间戳

首页 > 解决方案 > 获取mysql中没有数据或空值的表列

mysql - 获取mysql中没有数据或空值的表列

问题描述

我有一些表，我从中获取数据进行分析。我需要一些解决方法来查找列中包含空数据或空值的记录。困难的部分是我需要消除包含数据的记录列。请参阅下面的示例以更好地理解。

+----+------+------+------+------+
| ID | col1 | col2 | col3 | col4 |
+----+------+------+------+------+
| 1  | Val1 | Val2 | Val3 | NULL |
| 2  | NULL | Val2 | NULL | Val4 |
| 3  | Val1 | Val2 | Val3 |      |
+----+------+------+------+------+

是否可以使用查询获得如下输出？

+------+------+------+
| 1    |  2   |   3  |
+------+------+------+
| col4 | col1 | col4 |
|      | col3 |      |
|      |      |      |
+------+------+------+

标签： mysqlsqldatabaseselect

解决方案

在 MySQL 5.6.37 上测试：

select 
 max(if(d.rownum=r.rownum and d.id=1,d.val,null)) as `1`,
 max(if(d.rownum=r.rownum and d.id=2,d.val,null)) as `2`,
 max(if(d.rownum=r.rownum and d.id=3,d.val,null)) as `3`
from (select 1 as rownum union select 2 union select 3 union select 4) as r
left join (
 select t.id, t.val, @n:=if(t.id=@id,@n+1,1) as rownum, @id:=t.id
 from (select @n:=0 as n, @id:=0 as id) as _init
 cross join (
  select id, 'col1' as val from mytable where col1 is null
  union select id, 'col2' from mytable where col2 is null
  union select id, 'col3' from mytable where col3 is null
  union select id, 'col4' from mytable where col4 is null
  order by id, val) as t) as d
   on r.rownum = d.rownum
group by r.rownum;

输出：

+------+------+------+
| 1    | 2    | 3    |
+------+------+------+
| col4 | col1 | col4 |
| NULL | col3 | NULL |
| NULL | NULL | NULL |
| NULL | NULL | NULL |
+------+------+------+

推荐阅读