MS SQL查询正在比较不可用的数据. (除以零错误) [英] MS SQL Query is comparing data that is not available. (Divide by zero error encountered)

问题描述

我在(当然)SQL中遇到了一个非常奇怪的问题.

我有一张桌子(测试):

Hi,

I''ve got a really weird problem in (ofcourse) SQL.

I have a table(test):

ID     Total    Amount
1      10       3
2      10       2
3      10       1
4      10       0

现在，我要执行以下代码:

Now I want to execute the following code:

SELECT *
FROM test
WHERE Total / Amount = 5

但是，如果运行此查询，由于最后一条记录将尝试计算"10/0"，因此它将给出遇到的零除错误".当然这不起作用，但是没有办法使其起作用，请尝试以下操作:

But if I run this query it will give a "Divide by zero error encountered" because of the last record where it will try to calculate "10 / 0". Of course this doesn''t work, but there is no way to get it to work, tried this:

SELECT *
FROM 
(
   SELECT *
   FROM test
   WHERE Amount > 0
) a
WHERE a.Total / a.Amount = 5

但是，即使那样也不行.

请帮助...

But even that doesn''t work.

Please help...

推荐答案

尝试:

SELECT 
  *
FROM 
  test
WHERE 
  (Total / NULLIF(Amount, 0)) = 5

这确实有效，WEIRD SQL SERVER !!!!

This does work, WEIRD SQL SERVER!!!!

SELECT *
FROM test
WHERE Amount > 0
AND Total / Amount = 5

这篇关于MS SQL查询正在比较不可用的数据. (除以零错误)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！