使用sum返回的值高出3倍时，结果是错误的

nvbavucw 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(363)

ALTER PROCEDURE [dbo].[STOR_totalforadmin]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- Interfering with SELECT statements.

    SET NOCOUNT ON;

    -- SELECT * FROM tblDraw         

    SELECT tblretailer.ID,Name as retailername, tblretailer.AbcRate,   tblretailer.AbRate, 
           tblretailer.BoxRate, tblretailer.StraightRate,
           sum(isnull(ACount, 0)) as atotal ,
           sum(isnull(BCount, 0)) as Btotal,
           sum(isnull(CCount, 0)) as Ctotal,
           SUM(isnull(Count,0)) as strtotal,
           SUM(isnull(BoxCount,0)) as boxtotal,
           SUM(isnull(ABCount,0))as abtotal,
           SUM(isnull(ACCount,0))as actotal,
           SUM(isnull(BCCount,0)) as bctotal
    FROM tblretailer  FULL JOIN Tbl_ABC abc  ON (abc.RetailerID=tblretailer.ID)
    FULL JOIN TblDraw  Draw ON (Draw.RetailerID=tblretailer.ID)
    FULL JOIN Tbl_ABACBC abbc  ON (abbc.RetailerID=tblretailer.ID) 
    GROUP BY Name, tblretailer.ID, tblretailer.AbcRate, tblretailer.AbRate, tblretailer.BoxRate, tblretailer.StraightRate
END

sql sql-server stored-procedures

来源：https://stackoverflow.com/questions/62016355/results-are-wrong-when-using-sum-returns-values-3-times-higher

2条答案

按热度按时间

xbp102n01#

假设您有两个表，如下所示：

declare @table1 table(Id int, Value int)
declare @table2 table(Id int, Value int)

我们插入如下值：

insert into @table1(Id, Value) values (1, 10)
insert into @table1(Id, Value) values (2, 20)
insert into @table1(Id, Value) values (3, 30)

insert into @table2(Id, Value) values (1, 10)
insert into @table2(Id, Value) values (1, 20)
insert into @table2(Id, Value) values (2, 30)

首先，我们运行以下查询以获取摘要：

select
        t1.Id,
        SUM(ISNULL(t1.Value, 0)) t1Sum,
        SUM(ISNULL(t2.Value, 0)) t2Sum
from
        @table1 t1
        full join @table2 t2 on t1.id = t2.id
group by
        t1.Id

结果：

Id    t1Sum    t2Sum
1     20       30
2     20       30
3     30       0

表1中的id 1的和值为10，但为什么结果是20？
然后运行以下查询（无摘要和分组依据）：

select  
        *
from
        @table1 t1 full join @table2 t2 on t1.id = t2.id

结果：

Id    Value     Id    Value
1     10        1     10
1     10        1     20
2     20        2     30
3     30        NULL  NULL

因为我们使用 full join ，用录制 Id = 1 在 @table1 因为有两条记录 Id = 1 在@表2中。

赞(0）回复(0）举报 2021-07-26

iezvtpos2#

将总和除以3，如下所示：

sum(isnull(ACount, 0))/3

为了所有人。

赞(0）回复(0）举报 2021-07-26

我来回答

使用sum返回的值高出3倍时，结果是错误的

2条答案

相关问题

热门标签

最新问答