这个 SQL 查询是什么意思？

发布于 2024-09-08 02:54:09 字数 229 浏览 11 评论 0原文

我有以下 SQL 查询：

select AuditStatusId 
from dbo.ABC_AuditStatus 
where coalesce(AuditFrequency, 0) <> 0

我有点难以理解它。它看起来很简单，我知道合并运算符的作用（或多或少），但似乎不明白其含义。

除了上面的查询之外，不知道更多信息，您认为这意味着什么？

原文

I have the following SQL query:

select AuditStatusId 
from dbo.ABC_AuditStatus 
where coalesce(AuditFrequency, 0) <> 0

I'm struggling a bit to understand it. It looks pretty simple, and I know what the coalesce operator does (more or less), but dont' seem to get the MEANING.

Without knowing anymore information except the query above, what do you think it means?

分享到QQ

分享到微博

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

发布评论

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

一人独醉 2024-09-15 02:54:09

select AuditStatusId 
from dbo.ABC_AuditStatus 
where AuditFrequency  <> 0 and AuditFrequency  is not null

请注意，使用 Coalesce 意味着将无法正确使用索引来满足此查询。

select AuditStatusId 
from dbo.ABC_AuditStatus 
where AuditFrequency  <> 0 and AuditFrequency  is not null

Note that the use of Coalesce means that it will not be possible to use an index properly to satisfy this query.

回复收藏 0 原文

奢欲 2024-09-15 02:54:09

COALESCE 是处理 NULL 值的 ANSI 标准函数，它根据逗号分隔列表返回第一个非 NULL 值。这：

WHERE COALESCE(AuditFrequency, 0) != 0

..意味着如果 AuditFrequency 列为 NULL，则将该值转换为零。否则，将返回 AuditFrequency 值。

由于比较的目的是不返回 AuditFrequency 列值为零的行，因此 AuditFrequency 为 NULL 的行也将被查询忽略。

COALESCE is the ANSI standard function to deal with NULL values, by returning the first non-NULL value based on the comma delimited list. This:

WHERE COALESCE(AuditFrequency, 0) != 0

..means that if the AuditFrequency column is NULL, convert the value to be zero instead. Otherwise, the AuditFrequency value is returned.

Since the comparison is to not return rows where the AuditFrequency column value is zero, rows where AuditFrequency is NULL will also be ignored by the query.

回复收藏 0 原文

心意如水 2024-09-15 02:54:09

看起来它的设计目的是将 null AuditFrequency 检测为零，从而隐藏这些行。

回复收藏 0 原文

陈独秀 2024-09-15 02:54:09

据我所见，它检查不为 0 或 null 的字段。

回复收藏 0 原文

甜｀诱少女 2024-09-15 02:54:09

我认为这样描述更准确：

select AuditStatusId 
from dbo.ABC_AuditStatus 
where (AuditFrequency IS NOT NULL AND AuditFrequency != 0) OR 0 != 0

我承认最后一部分永远不会做任何事情，也许我只是迂腐，但对我来说，这更准确地描述了您的查询。

I think it is more accurately described by this:

select AuditStatusId 
from dbo.ABC_AuditStatus 
where (AuditFrequency IS NOT NULL AND AuditFrequency != 0) OR 0 != 0

I'll admit the last part will never do anything and maybe i'm just being pedantic but to me this more accurately describes your query.

回复收藏 0 原文

蘸点软妹酱 2024-09-15 02:54:09

这个想法是希望使用单个表达式来表达单个搜索条件，但这只是风格，品味问题：

一个表达式：

WHERE age = COALESCE(@parameter_value, age);

两个表达式：

WHERE (
       age = @parameter_value
       OR
       @parameter_value IS NULL
      );

这是另一个例子：

一个表达式：

WHERE age BETWEEN 18 AND 65;

两个表达式

WHERE (
       age >= 18
       AND 
       age <= 65
      );

就我个人而言，我有很强的个人偏好对于单个表达式并发现它们更容易阅读...如果我熟悉所使用的模式;)它们是否表现不同是另一回事...

The idea is that it is desireable to express a single search condition using a single expression but it's merely style, a question of taste:

One expression:

WHERE age = COALESCE(@parameter_value, age);

Two expressions:

WHERE (
       age = @parameter_value
       OR
       @parameter_value IS NULL
      );

Here's another example:

One expression:

WHERE age BETWEEN 18 AND 65;

Two expressions

WHERE (
       age >= 18
       AND 
       age <= 65
      );

Personally, I have a strong personal perference for single expressions and find them easier to read... if I am familiar with the pattern used ;) Whether they perform differently is another matter...

回复收藏 0 原文

~没有更多了~