mysql - mysql查询过滤掉NULL或空白行

问题描述

我是编写sql的新手，所以提前感谢您提供的任何帮助。我在 mysql 数据库中有一个表（table1），它的视图如下：

+-----+----------+------------+
| id  | key      | value      |
+-----+----------+------------+
|   1 | name     | Bob        |
|   1 | location | ABC        |
|   1 | date     | xxxx-xx-xx |
|   2 | name     | Jim        |
|   2 | location | MID        |
|   2 | date     |            |
|   3 | name     |            |
|   3 | location |            |
|   3 | date     |            |
|   4 | name     | Sue        |
|   4 | location | DFW        |
|   4 | date     | xxxx-xx-xx |
|   5 | name     | Sue        |
|   5 | location |            |
|   5 | date     | xxxx-xx-xx |
|   6 | name     | Bob        |
|   6 | location | GRE        |
|   6 | date     | xxxx-xx-xx |
|   7 | name     |            |
|   7 | location |            |
|   7 | date     |            |
+-----+----------+------------+

我创建了一个视图，我基本上将行反转（旋转）到列，如下所示：

+-----+-------+----------+------------+
| id  | name  | location | date       |
+-----+-------+----------+------------+
|   1 | Bob   | ABC      | xxxx-xx-xx |
|   2 | Jim   | MID      |            |
|   3 |       |          |            |
|   4 | Sue   | DFW      | xxxx-xx-xx |
|   5 | Sue   |          | xxxx-xx-xx |
|   6 | Bob   | GRE      | xxxx-xx-xx |
|   7 |       |          |            |
|   8 | Bob   | DFW      | xxxx-xx-xx |
|   9 |       |          |            |
|  10 | Joe   | DFW      | xxxx-xx-xx |
|  11 |       |          |            |
|  12 |       |          |            |
|  13 |       |          |            |
|  14 | Jim   | SUS      | xxxx-xx-xx |
+-----+-------+----------+------------+

这是我如何做到这一点的代码：

select c.`id`, max(ifnull(c.name,NULL)) as name, max(ifnull(c.location,NULL)) as location, max(ifnull(c.date,NULL)) as date from (
select `id`,
case when `key` = 'name' then value end as name,
case when `key` = 'location' then value end as location,
case when `key` = 'date' then value end as date
  from `table1`
  WHERE value != ''
) c group by `id`

我需要从视图中过滤掉名称、位置和日期为 NULL 或空白的行。通过添加，我能够清除大约一半的行WHERE value != ''，但是如何过滤掉其余的行？WHERE value IS NOT NULL似乎没有帮助。

标签： mysqlnullwhere-clause

mysql - mysql查询过滤掉NULL或空白行

问题描述

解决方案

推荐阅读