Django中的初学者SQL IF条款 [英] Beginner SQL IF Clause in Django

问题描述

我需要能够在Django中处理 Division by Zero 错误。以下SQL导致它：

  select *，（available_credit * 100 / credit_limit）:: integer作为percent_available从
（SELECT 
 a.id，
  

在第一行，信用限额有时会为0，我希望能够处理这种情况，以便生成的 percent_available 变量可以为0。

我尝试用我的一些已知的SQL查找修补程序，但显然不起作用：

 IF（credit_limit = 0）
 0 ::从
中的percent_available的整数ELSE 
（available_credit * 100 / credit_limit）::以$ b $的百分比b（SELECT 
 a.id，
  

有没有可行的方法？ / p>

解决方案

您可以使用CASE，WHEN为此，如果您正在编写原始的sql并获得输出是相当容易，但django调用一个一点点工作。

  SELECT A. *，CASE WHEN credit_limit = 0 THEN 10000 
 ELSE 
（available_credit * 100 / credit_limit）:: integer 
从
（SELECT 
 a.id， ....）A A 
  

当credit_limit为零。你会以N / A或其他方式向用户报告。

回到django，您将使用 CASE 表达式。

一个Case（）表达式就像在
Python中的if ... elif ... else语句。提供的When（）对象中的每个条件都以
顺序进行计算，直到求值为真值为止。返回匹配的When（）对象的结果表达式
。

现在假设你的模型被命名为Credit，那么查询可能是。

  Credit.objects.all（）。annotate（credit_available = 
 Case（
当（credit_limit = 0，那么值10000）
 default = F（'available_credit'）* 100 / F（'credit_limit'））
）
  pre> 
I need to be able to handle a Division By Zero Error in Django. The following SQL is causing it:
select *, (available_credit * 100 / credit_limit)::integer as percent_available from
(SELECT
  a.id,
On the first line, credit limit for people will sometimes be 0, and I want to be able to handle that scenario so that the resulting percent_available variable can be 0.

I tried to find a fix with my little known SQL but it obviously doesn't work:
IF (credit_limit = 0)
  0::integer as percent_available from
ELSE
  (available_credit * 100 / credit_limit)::integer as percent_available from
(SELECT
  a.id,
Is there a feasible way to do this?
解决方案
You can use CASE, WHEN for this if you are writing raw sql and getting the output is rather easy but django calls for a bit of work.
SELECT A.*, CASE WHEN credit_limit = 0 THEN 10000
ELSE
  (available_credit * 100 / credit_limit)::integer 
END as percent_available from
(SELECT
  a.id, ....) AS A
It would be wholly incorrect to report available percentage as zero when credit_limit is zero. You shoudl report it to the user as N/A or something else.

Getting back to django, you would use the CASE expression for this. 

  
A Case() expression is like the if ... elif ... else statement in
  Python. Each condition in the provided When() objects is evaluated in
  order, until one evaluates to a truthful value. The result expression
  from the matching When() object is returned.
Now suppose your model was named Credit, then the query might be.
Credit.objects.all().annotate(credit_available = 
   Case(
     When(credit_limit = 0, Then value 10000)
     default = F('available_credit')*100/F('credit_limit'))
 )


                        
这篇关于Django中的初学者SQL IF条款的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！