SQLITE SELECT 一个日期整数列，如果该日期的所有记录在另一个参数中均为 NULL

发布于 2024-11-27 14:14:16 字数 568 浏览 4 评论 0原文

我可能以错误的方式处理这个问题...

我有一个表，其中日期为 INTEGER，格式为 yyyymmdd - 名为 DateInteger 我有另一个字段Method，默认情况下为NULL。该字段由各种查询更新。我想生成一份报告，说明所有 Method 字段均为 NULL 的日子（按 DateInteger 分组/排序）。

`SELECT DateInteger,COUNT(*) FROM Table WHERE Method IS NOT NULL GROUP BY DateInteger HAVING Count(*)=0 ORDER BY DateInteger ASC;` doesn't give any results, as the days which have only `NULL` values seem to be missed out...

我如何使查询包含/查看这些天？

TIA

原文

I'm probably approaching this the wrong way...

I have a table with the Date as an INTEGER in format yyyymmdd - named DateInteger
I have another field Method which is NULL by default. This field is updated by various queries.
I'd like to produce a report that illustrates the days where ALL the Method fields are NULL (Grouping/Ordering by DateInteger).

`SELECT DateInteger,COUNT(*) FROM Table WHERE Method IS NOT NULL GROUP BY DateInteger HAVING Count(*)=0 ORDER BY DateInteger ASC;` doesn't give any results, as the days which have only `NULL` values seem to be missed out...

How do I make the query include / look at these days ?

TIA

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

就是爱搞怪 2024-12-04 14:14:16

我想生成一份报告，说明所有方法字段均为 NULL 的日期（按 DateInteger 分组/排序）。

这将返回一组不同的日期字段值，其中方法列值为 < em>not null：

            select distinct yourDateField from yourTable 
           where method is not null

如果我理解您的意思，上面的这些日期字段值是您不想要的值：您需要给定日期的每一行中的所有方法值都为 null 。因此，您想要的日期字段值不能位于上面的集合中。

          select T.yourDateField, NotWanted.Unwanted
          from yourTable as T
          left join
          (
            select distinct yourDateField as Unwanted from yourTable 
           where method is not null
          ) as NotWanted

          on T.yourDateField = NotWanted.Unwanted

          where NotWanted.Unwanted is null

编辑：你也可以尝试这个：

        select T.yourDateField from yourTable as T
        where not exists
        (
           select yourDateField from yourTable as T2
           where T1.yourDateField = T2.yourDateField
           and T2.method is not null

        )

I'd like to produce a report that illustrates the days where ALL the Method fields are NULL (Grouping/Ordering by DateInteger).

This will return a distinct set of datefield values where the method column value is not null:

            select distinct yourDateField from yourTable 
           where method is not null

If I understand you, these datefield values above are the ones you do not want: you need all of the method values in every row with a given date to be null. Thus, the datefield values you do want cannot be in the set above.

          select T.yourDateField, NotWanted.Unwanted
          from yourTable as T
          left join
          (
            select distinct yourDateField as Unwanted from yourTable 
           where method is not null
          ) as NotWanted

          on T.yourDateField = NotWanted.Unwanted

          where NotWanted.Unwanted is null

EDIT: you might also try this:

        select T.yourDateField from yourTable as T
        where not exists
        (
           select yourDateField from yourTable as T2
           where T1.yourDateField = T2.yourDateField
           and T2.method is not null

        )

回复收藏 0 原文

~没有更多了~