代码之家  ›  专栏  ›  技术社区  ›  Jon

SQL Server舍入问题寻找解释

  •  1
  • Jon  · 技术社区  · 15 年前

    SELECT name, number,  
    CASE WHEN number = 0 THEN 0 ELSE
    convert(Numeric(10,2), number / CONVERT(decimal(5,2),SUM(number)) * 100)
    END as "Percentage of Total" 
    FROM #names 
    group by name, number
    

    我收到的结果是:

    name                      number      Percentage of Total
    ------------------------- ----------- ---------------------------------------
    Test 1                      0           0.00
    Test 2                     22          100.00
    Test 3                     28          100.00
    

        declare @total decimal(5,2)
    
        select @total = SUM(number) FROM #names
    
        SELECT name, number, convert(Numeric(10,2), number/ @total * 100) as "Percentage of Total"  
        FROM #names
        group by name, number
    

    正确结果:

    name                      number      Percentage of Total
    ------------------------- ----------- ---------------------------------------
    Test 1                     22          44.00
    Test 2                      0           0.00
    Test 3                     28          56.00
    

    乔恩

    1 回复  |  直到 15 年前
        1
  •  1
  •   Quassnoi    15 年前

    您首先可以按编号查询组。

    number / SUM(number) 相当于 1 / COUNT 0 ).

    第二个查询不按数字分组,而是计算总和。

    SELECT  name, number * 100.0 / SUM(number) OVER ()
    FROM    #names
    

    与…一起使用时 OVER 条款 SUM 成为分析函数而不是聚合函数。

    它不会将多个记录收缩为一个:而是返回每个记录的总值:

    -- This is an aggregate function. It shrinks all records into one record and returns the total sum
    
    WITH    q (name, number) AS
            (
            SELECT  'test1', 0
            UNION ALL
            SELECT  'test2', 22
            UNION ALL
            SELECT  'test3', 28
            )
    SELECT  SUM(number)
    FROM    q
    
    --
    50
    

    -- This is an analytical function. It calcuates the total sum as well but does not shrink the records.
    
    WITH    q (name, number) AS
            (
            SELECT  'test1', 0
            UNION ALL
            SELECT  'test2', 22
            UNION ALL
            SELECT  'test3', 28
            )
    SELECT  SUM(number) OVER ()
    FROM    q
    
    --
    50
    50
    50