SQL Server中的累计计数计算

Question

假设我有一个包含两列的表- ID，money。我想做一个查询，看看有多少ID的钱少于100,200,300，...，10000，我应该如何在SQL Server中做到这一点？我需要使用变量吗？

Answer 1

有时在存储过程中使用循环是最简单的：

CREATE TABLE #tmp0(
bucket int,
accumulator int,
)

/* for(n = maxvalue; n > 0; n -= 100) */

Declare @n int
select @n = (select 100 + (100 * (max(moneyField) / 100)) from tbl)
while @n > 0
begin
    insert into #tmp0 (bucket, accumulator) 
        (select @n, count(*) from tbl where moneyField < @n)
    select @n = @n - 100
end
select * from #tmp0 order by bucket

drop table #tmp0

Answer 2

假设id是一个键列，尝试如下所示：

Select Case When money < 100 Then 'LT100'
            When money < 200 Then 'From100To199'
            When money < 300 Then 'From200To299'
            When money < 1000 Then 'From300To999'
                              Else 'GE1000' End Bucket, 
       Count(*) Count
From Table
Group By Case When money < 100 Then 'LT100'
            When money < 200 Then 'From100To199'
            When money < 300 Then 'From200To299'
            When money < 1000 Then 'From300To999'
                              Else 'GE1000' End  

编辑:如果存储桶的长度或大小是一个常量(或者可以表示为SQL表达式)，则可以通过将存储桶定义为该表达式的输出来执行任意数量的存储桶，例如，定义每$100美元存储桶：

Select Str(Floor(Cast(money / 100)), 8, 0)  Bucket, 
       Count(*) Count
From Table
Group By  Str(Floor(Cast(money / 100)), 8, 0)

或者，从100美元桶到1000美元，然后是1000美元桶：

Select Case When Money < 1000 
            Then  Str(Floor(Cast(money / 100)), 8, 0) 
            Else Str(Floor(Cast(money / 1000)), 8, 0) End  Bucket, 
       Count(*) Count
From Table
Group By Case When Money < 1000 
            Then  Str(Floor(Cast(money / 100)), 8, 0) 
            Else Str(Floor(Cast(money / 1000)), 8, 0) End 

要获得累计计数，我将使用上述SQL查询的输出作为另一个SQL中的子查询:以第一个SQL为例：

  Select LT100,
         LT100 + From100To199 LT200,
         LT100 + From100To199 + From200To299  LT300,
         LT100 + From100To199 +  From200To299 + From300To999  LT1000,
         LT100 + From100To199 +  From200To299 + From300To999 + GE1000 Total     
  From ( Select Case When money < 100 Then 'LT100'
                When money < 200 Then 'From100To199'
                When money < 300 Then 'From200To299'
                When money < 1000 Then 'From300To999'
                                  Else 'GE1000' End Bucket, 
           Count(*) Count
         From Table
         Group By Case When money < 100 Then 'LT100'
                When money < 200 Then 'From100To199'
                When money < 300 Then 'From200To299'
                When money < 1000 Then 'From300To999'
                                  Else 'GE1000' End) Z

Answer 3

SELECT
     SUM(CASE WHEN MONEY < 100 THEN 1 ELSE 0 END AS '0-100'
    ,SUM(CASE WHEN MONEY >= 100 AND MONEY < 200 THEN 1 ELSE 0 END AS '100-200'
    ,SUM(CASE WHEN MONEY >= 200 AND MONEY < 300 THEN 1 ELSE 0 END AS '200-300'
FROM
    Table