mysql - NOT IN 意外地没有产生任何行

Question

我有一个用户定义的函数 udf，它在 MariaDB 5.5 中返回一个 bool 值。

以下给出了我的期望:

select c, count(*)
from (
    select fld, count(*)c
    from tbl
    where udf(fld)
    group by fld
) t
group by c;

+---+----------+
| c | count(*) |
+---+----------+
| 1 |    12345 |
| 2 |     1234 |
| 3 |      123 |
| 4 |       12 |
| 5 |        1 |
+---+----------+
5 rows in set (26.75 sec)

同样，下面给出了数字12345(来自上面的表格)，正如我所期望的:

select anotherfield, count(*)
from tbl
where udf(fld)
and fld in (
    select fld from (
        select fld,count(*)c
        from tbl
        group by fld
        having c=1
    )t
)
group by anotherfield with rollup;

我希望以下内容也会给我12345:

select anotherfield, count(*)
from tbl
where udf(fld)
and fld not in (
    select fld from (
        select fld,count(*)c
        from tbl
        group by fld
        having c>1
    )t
)
group by anotherfield with rollup;

但是，它没有给我任何行。为什么？

Answer 1

正如评论中提到的，如果子查询返回的任何行为NULL，那么您将不会返回任何行。一种解决方案是显式过滤它们:

where fld not in (select fld 
                  from tbl
                  where fld is not null
                  group by fld
                  having count(*) > 1 
                )

我的首选方法是使用 not contains 因为它具有正确的语义:

where not exists (select 1 
                  from tbl t2
                  group by fld
                  having count(*) > 1 and
                         tbl.fld = t2.fld
                 )

也就是说，更有效的方法通常是查找行中的一些差异，而不是检查 count(*)。也就是说，它不会获取具有相同字段的所有行，而是在获取第二行时停止:

where not exists (select 1 
                  from tbl t2
                  where tbl.fld = t2.fld and
                        tbl.id <> t2.id -- or some appropriate column
                 )