首页 / ORACLE / MySql,Postgres,Oracle和SQLServer忽略IS NOT NULL过滤器

MySql,Postgres,Oracle和SQLServer忽略IS NOT NULL过滤器

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySql,Postgres,Oracle和SQLServer忽略IS NOT NULL过滤器，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3408字，纯文字阅读大概需要5分钟。

内容图文

当我准备在这里回答我们其中一位同伴的问题时,我遇到了一个奇怪的情况,至少对我而言.原来的问题在这里：Pivot Table Omitting Rows that Have Null values

我已修改查询以使用max而不是group_concat,以便在所有数据库中显示“问题”.

SELECT 
  id, 
  max(case when colID = 1 then value else '' end) AS fn,
  max(case when colID = 2 then value else '' end) AS ln,
  max(case when colID = 3 then value else '' end) AS jt
FROM tbl 
GROUP BY id

该查询的结果是这样的：

ID    FN        LN            JT
1    Sampo    Kallinen     Office Manager
2    Jakko    Salovaara    Vice President
3    (null)   Foo          No First Name

用户要求过滤ID为3的行,因为该字段值为null.

很明显,仅需要做的就是在该查询上添加WHERE值IS NOT NULL约束以实现用户期望的结果.它不会工作.

因此,我开始在其他数据库上对其进行测试,以查看会发生什么情况(WHERE CLAUSE查询)

SELECT 
  id, 
  max(case when colID = 1 then value else '' end) AS fn,
  max(case when colID = 2 then value else '' end) AS ln,
  max(case when colID = 3 then value else '' end) AS jt
FROM tbl 
  WHERE value is not null
GROUP BY id

> MySQL：http://sqlfiddle.com/#!2/78395/1
> Postgres：http://sqlfiddle.com/#!15/78395/1
> SQLServer：http://sqlfiddle.com/#!6/78395/1
甲骨文：http://sqlfiddle.com/#!4/78395/1

令我惊讶的是结果是一样的,没有一个奏效.

然后,我尝试了同一查询的不同版本：

SELECT * FROM (
    SELECT 
      id, 
      max(case when colID = 1 then value else '' end) AS fn,
      max(case when colID = 2 then value else '' end) AS ln,
      max(case when colID = 3 then value else '' end) AS jt
    FROM tbl 
    GROUP BY id
) T
WHERE fn IS NOT NULL
  AND ln IS NOT NULL
  AND jt IS NOT NULL

> Oracle：已解决http://sqlfiddle.com/#!4/78395/2
> MySQL的：http://sqlfiddle.com/#!2/78395/2
> Postgres：http://sqlfiddle.com/#!15/78395/2
> SQLServer：http://sqlfiddle.com/#!6/78395/2

我可以使其在所有数据库上运行的唯一方法是使用此查询：

SELECT 
  id, 
  max(case when colID = 1 then value else '' end) AS fn,
  max(case when colID = 2 then value else '' end) AS ln,
  max(case when colID = 3 then value else '' end) AS jt
FROM tbl 
WHERE NOT EXISTS (SELECT * FROM tbl b WHERE tbl.id=b.id AND value IS NULL)
GROUP BY id

所以我问：
除了Oracle上的特定情况外,所有其他DB似乎都忽略了IS NOT NULL过滤器,这是怎么回事？

解决方法:

如果在相同ID的任何源行中有值IS NULL的情况下要从结果中省略该行,则Postgres中的解决方案是在HAVING子句中使用聚合函数every()或(出于历史原因,为同义词)bool_and()：

SELECT id
     , max(case when colID = 1 then value else '' end) AS fn
     , max(case when colID = 2 then value else '' end) AS ln
     , max(case when colID = 3 then value else '' end) AS jt
FROM   tbl 
GROUP  BY id
HAVING every(value IS NOT NULL);

SQL Fiddle.

说明

您尝试使用WHERE子句只会消除示例中ID = 3的一个源行(colID = 1的那一行),而对于相同的ID剩下两个.因此,汇总后,结果中仍会获得id = 3的行.

但是由于没有colID = 1的行,因此在id = 3的结果中,fn获得了一个空字符串(注意：不是NULL值！).

Postgres中更快的解决方案是使用crosstab().细节：

> PostgreSQL Crosstab Query

其他RDBMS

尽管在SQL：2008标准中定义了每个,但是许多RDBMS不支持它,大概是因为其中一些具有布尔类型的幕后实现. (不要删除任何名称,例如“ MySQL”或“ Oracle” …).您可能可以在任何地方(包括Postgres)替换为：

SELECT id
     , max(case when colID = 1 then value else '' end) AS fn
     , max(case when colID = 2 then value else '' end) AS ln
     , max(case when colID = 3 then value else '' end) AS jt
FROM   tbl 
GROUP  BY id
HAVING count(*) = count(value);

因为count()不计算NULL值.在MySQL中也有bit_and().
有关此问题的更多信息：

> Is there any equivalent to Postgresql EVERY aggregate function on other RDBMS?

内容总结

以上是互联网集市为您收集整理的MySql,Postgres,Oracle和SQLServer忽略IS NOT NULL过滤器全部内容，希望文章能够帮你解决MySql,Postgres,Oracle和SQLServer忽略IS NOT NULL过滤器所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/886851.html

来源：【匿名】