sql - 在select语句中怎么划分？

Question

我正在尝试获取 select 语句中两列的商。这是我尝试过的。

Select ( select sum(x.amount) amount
         from this_table x
         where x.acct_no = '52'
       ) / 
       ( select sum(x.amount) amount
         from this_table x
         where x.acct_no = '53'
       ) as amount
from this_table y
join this_table x on x.acct_no = y.acct_no
where x.acct_no = '52' or x.acct_no = '53'

当我执行此操作时，我的金额列刚好显示为 1，我敢肯定这不是应该的结果。有什么建议或帮助吗？解决方案？

Answer 1

这应该如你所愿:

select  sum(case when acct_no = '52' then amount else 0 end)
      / sum(case when acct_no = '53' then amount else 0 end) as amount
from    this_table
where   acct_no in ('52','53')

要使金额列在除数为零时显示为零(而不是返回错误)，您可以使用:

select  case when sum(case when acct_no = '53' then amount else 0 end) = 0 then 0
        else
        sum(case when acct_no = '52' then amount else 0 end)
      / sum(case when acct_no = '53' then amount else 0 end) end as amount
from    this_table
where   acct_no in ('52','53')

如果您计划经常这样做和/或在您不希望向您抛回错误的应用程序中使用结果，这可能会很有用。