除以零如何处理选择语句 [英] Division by zero how do you handle it a select statement

问题描述

亲爱的专家，



如果发生这种情况，你如何处理零除以使它不会产生问题

选择声明



如下图所示



（（Q1Y2C - Q1Y1C）* 100）/ Q1Y1C DIFFCQ1P ，

（（Q2Y2C - Q2Y1C）* 100）/ Q2Y1C DIFFCQ2P，

（（Q3Y2C - Q3Y1C）* 100）/ Q3Y1C DIFFCQ3P，

（（Q4Y2C - Q4Y1C）* 100）/ Q4Y1C DIFFCQ4P





这些是

Q1Y1C，

Q2Y1C，

Q3Y1C，

Q4Y1C







SELECT BRNNAME，SCORE，

Q1Y1C，Q1Y2C，

Q2Y1C，Q2Y2C，

Q3Y1C，Q3Y2C ，

Q4Y1C，Q4Y2C，

Q1Y1P，Q1Y2P，

Q2Y1P，Q2Y2P，

Q3Y1P，Q3Y2P，

Q4Y1P，Q4Y2P，

（Q1Y2C - Q1Y1C）DIFFCQ1C，

（Q2Y2C - Q2Y1C）DIFFCQ2C，
（Q4Y2C - Q4Y1C）DIFFCQ4C，

（（Q2Y2C - Q2Y1C）* 100）/ Q2Y1C DIFFCQ2P，

（（Q3Y2C - Q3Y1C）* 100）/ Q3Y1C DIFFCQ3P，

（（Q4Y2C - Q4Y1C）* 100）/ Q4Y1C DIFFCQ4P

来自BRNNAME的YFINAL订单





除以零



（（Q1Y2C - Q1Y1C）* 100）/ Q1Y1C DIFFCQ1P，

（ （Q2Y2C - Q2Y1C）* 100）/ Q2Y1C DIFFCQ2P，

（（Q3Y2C - Q3Y1C）* 100）/ Q3Y1C DIFFCQ3P，

（（Q4Y2C - Q4Y1C）* 100） / Q4Y1C DIFFCQ4P



我的尝试：



继续sql服务器开发挑战

Dear Expert,

How do you handle division by Zero should it occur so that it doesn't create a problem with
the Select statement

as depicted below

((Q1Y2C - Q1Y1C)*100)/Q1Y1C DIFFCQ1P ,
((Q2Y2C - Q2Y1C)*100)/Q2Y1C DIFFCQ2P ,
((Q3Y2C - Q3Y1C)*100)/Q3Y1C DIFFCQ3P ,
((Q4Y2C - Q4Y1C)*100)/Q4Y1C DIFFCQ4P


These are
Q1Y1C ,
Q2Y1C ,
Q3Y1C ,
Q4Y1C



SELECT BRNNAME, SCORE ,
Q1Y1C , Q1Y2C ,
Q2Y1C , Q2Y2C ,
Q3Y1C , Q3Y2C ,
Q4Y1C , Q4Y2C ,
Q1Y1P , Q1Y2P ,
Q2Y1P , Q2Y2P ,
Q3Y1P , Q3Y2P ,
Q4Y1P , Q4Y2P ,
(Q1Y2C - Q1Y1C ) DIFFCQ1C ,
(Q2Y2C - Q2Y1C ) DIFFCQ2C ,
(Q3Y2C - Q3Y1C ) DIFFCQ3C ,
(Q4Y2C - Q4Y1C ) DIFFCQ4C ,
((Q1Y2C - Q1Y1C)*100)/Q1Y1C DIFFCQ1P ,
((Q2Y2C - Q2Y1C)*100)/Q2Y1C DIFFCQ2P ,
((Q3Y2C - Q3Y1C)*100)/Q3Y1C DIFFCQ3P,
((Q4Y2C - Q4Y1C)*100)/Q4Y1C DIFFCQ4P
FROM YFINAL ORDER BY BRNNAME


Division by Zero

((Q1Y2C - Q1Y1C)*100)/Q1Y1C DIFFCQ1P ,
((Q2Y2C - Q2Y1C)*100)/Q2Y1C DIFFCQ2P ,
((Q3Y2C - Q3Y1C)*100)/Q3Y1C DIFFCQ3P,
((Q4Y2C - Q4Y1C)*100)/Q4Y1C DIFFCQ4P

What I have tried:

On going sql server developmental challenges

推荐答案

说实话，我不会在这种情况下在服务器端执行计算 - 除非你这样做是一个set语句这会影响INSERT或UPDATE之类的东西，这应该在客户端执行，因为这看起来很像业务规则应该定义你想要的东西o。例如，您可能希望客户端默认将零字段除以0，或者您可以更新业务实体以表示缺少信息。

To be honest, I wouldn't perform the calculation at the server side in a situation like this - unless you were doing this as a set statement that affected something like an INSERT or UPDATE, this is something that should be performed on the client side as this looks suspiciously like something that a business rule should define what you want to do with. For instance, you might want the client side to default divide by zero fields to 0 or you might update a business entity to say that information was missing.

除了解决方案＃1之外< a href =https://www.codeproject.com/script/Membership/View.aspx?mid=213147> Pete O'Hanlon [ ^ ]（完全同意），您可以在服务器端执行此操作，但您必须检查是否除数大于零。例如：

In addition to solution #1 by Pete O'Hanlon[^] (completely agree), you can do that on server side, but you have to check if divisor is bigger than zero. For example:

WHERE COALESCE(Q1Y1C,0)>0 AND COALESCE(Q2Y1C,0)>0 AND COALESCE(Q3Y1C,0)>0 AND COALESCE(Q4Y1C, 0)>0 

最简单的选择是使除数为空而不是 0 。这样，除法将返回 Null 而不是抛出错误。

The simplest option is to make the divisor Null instead of 0. That way, the division will return Null rather than throwing an error.

((Q1Y2C - Q1Y1C) * 100) / NullIf(Q1Y1C, 0) As DIFFCQ1P

这篇关于除以零如何处理选择语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！