SQL四舍五入没有小数点的整数

Question

我有一个看似简单的问题。我只想显示没有小数位的百分比，并将总数加到 100。这是一个小片段:

create table genderTable
(person varchar(10),
isMale varchar(5))

insert into genderTable values ('Mary', 'false')
insert into genderTable values ('Frank', 'true')
insert into genderTable values ('Bill', 'true')
insert into genderTable values ('Jessie', 'false')
insert into genderTable values ('Sue', 'false')
insert into genderTable values ('Beth', 'false')
insert into genderTable values ('Kris', 'false')

declare @total as int
set @total = 7
select
CASE isMale
   WHEN 'True' THEN 'Male'
   ELSE 'Female'
END as Gender,
CASE 
WHEN @total > 0 THEN ROUND((count(isMale) * 100 / @total), 0)
ELSE 0
END as GenderPercent
from genderTable
group by  isMale

总数相加为 99% 而不是 100%。我试过各种舍入，但要么得到一位小数，要么得到 99%。有什么帮助吗？请记住，在另一个例子中，我必须对有两个以上值的种族做同样的事情，所以从 100 中减去可能行不通......

Answer 1

对于您的特定问题，当我这样做时，我得到的数字加起来为 100:

select isMale, count(*), sum(count(*)) over (),
       round(100.0 * count(*) / sum(count(*)) over (), 0)
from genderTable t
group by isMale;

您的实现的实际问题是 SQL Server 进行整数运算。因此，表达式 ROUND((count(isMale) * 100/@total), 0) 正在使用整数除法进行计算——采用 floor()实现 round() 之前的比率。

有很多方法可以做你想做的事。与早期版本相比，它们在 SQL Server 2012+ 中更容易实现:

select isMale,
       round(100.0 * cnt / tot, 0) as p,
       (case when seqnum = 1
             then 100 - sum(round(100.0 * cnt / tot, 0)) over (order by seqnum desc rows between unbounded preceding and 1 preceding)
             else round(100.0 * cnt / tot, 0) 
        end) as p_tot_100
from (select isMale, count(*)*1.0 as cnt, sum(1.0*count(*)) over () as tot,
             row_number() over (order by isMale) as seqnum
      from genderTable t
      group by isMale
     ) t;

想法是对所有其他行的舍入版本求和，然后从其中一行的 100 中减去它。

编辑:

这些的“整数”版本:

select isMale, count(*), sum(count(*)) over (),
       cast(round(100.0 * count(*) / sum(count(*)) over (), 0) as int)
from genderTable t
group by isMale;

和:

select isMale,
       round(100.0 * cnt / tot, 0) as p,
       cast((case when seqnum = 1
                  then 100 - sum(round(100.0 * cnt / tot, 0)) over (order by seqnum desc rows between unbounded preceding and 1 preceding)
                  else round(100.0 * cnt / tot, 0) 
             end) as int) as p_tot_100
from (select isMale, count(*)*1.0 as cnt, sum(1.0*count(*)) over () as tot,
             row_number() over (order by isMale) as seqnum
      from genderTable t
      group by isMale
     ) t;

我非常确定即使使用浮点表示法，小整数也能准确表示，因此 round() 不会产生诸如 29.99999999999997 而不是 30 之类的值。

p>