Question

我如何在逐节与团体进行的询问中使用分点?

我使用服务器2008年2月2日至6日

我收到这一错误:

Cannot perform an aggregate function on an expression 
containing an aggregate or a sub query.

与此类似:

select 
    t1.sen, 
    sum(t1.d1)as d1, 
    sum(t1.d2)as d2, 
    sum(t1.d1+t1.d2) as d_sum,
    Round((sum((1000*(t1.d1+t1.d2))/(9500-(
       select sum(t2.t_shab+t2.t_rooz) 
       from tbl1 t2 
       where FCode=81 AND DCode=1 AND t2.sen<=t1.sen
    )))),1) as SSS 
from 
    tbl1 t1 
where 
    FCode = 81 
    AND DCode = 1

group by t1.sen

Answer 1

其真正方式

create function getSumBSen2(@pfcode INT, @pdcode INT, @pSen INT) returns int
as
begin
     declare @r int; 
     select 
        @r= sum(t2.t_shab + t2.t_rooz)
     from 
         tbl1 t2 
     where 
         t2.FCode = @pfcode 
     and t2.DCode = @pdcode 
     and t2.sen <= @pSen;

     return (@r);
end;
GO

select 
    t1.sen, 
    sum(t1.d1) as d1, 
    sum(t1.d2) as d2, 
    sum(t1.d1 + t1.d2) as d_sum,
    Round((sum((1000*(t1.d1+t1.d2)+0.01)/(9500-(dbo.getSumBSen2(t1.FCode, t1.DCode, t1.sen))))),1) as SSS 
from 
    tbl1 t1
where 
    t1.FCode = 81 
and t1.DCode = 1 
group by 
    t1.sen;

Answer 2

如果没有任何保证,如果你不工作,我知道,我会删除我的回答。

create function getSumBSen(@pfcode number, @pdcode number, @pSen number) returns number
as

begin
     declare @r number;
     select 
         @r =sum(t2.t_shab + t2.t_rooz) 
     from 
         tbl1 t2 
     where 
         t2.FCode = @pfcode 
     and t2.DCode = @pdcode 
     and t2.sen <= @pSen
     group by t2.FCode, t2.DCode;

     return (@r);
end;


select 
    t1.sen, 
    sum(t1.d1) as d1, 
    sum(t1.d2) as d2, 
    sum(t1.d1 + t1.d2) as d_sum,
    Round((sum((1000*(t1.d1+t1.d2))/(9500-getSumBSen(t1.FCode, t1.dcode, t1.sen)))),1) as SSS 
from 
    tbl1 t1
where 
    t1.FCode = 81 
and t1.DCode = 1 
group by 
    t1.sen;

<><>>>>><>>>><>>>>>>>

Function creation

此外,由于我的耳光太短,我实际上要确定这部法律。因此,我不得不再写几张杂草,以接受编码。

Answer 3

为此:

DECLARE @tbl1 AS TABLE
    (
     FCode INT
    ,DCode INT
    ,sen INT
    ,d1 INT
    ,d2 INT
    ,t_shab INT
    ,t_rooz INT
    ) ;

SELECT  *
FROM    (
         SELECT t1.sen
               ,SUM(t1.d1) AS d1
               ,SUM(t1.d2) AS d2
               ,SUM(t1.d1 + t1.d2) AS d_sum
               ,ROUND((SUM((1000 * (t1.d1 + t1.d2)) / (9500 - factor.factor))),
                      1) AS SSS
         FROM   @tbl1 AS t1
         INNER JOIN (
                     SELECT t1.sen
                           ,SUM(t2.t_shab + t2.t_rooz) AS factor
                     FROM   @tbl1 AS t2
                     INNER JOIN @tbl1 AS t1
                            ON t1.FCode = 81
                               AND t1.DCode = 1
                               AND t2.FCode = 81
                               AND t2.DCode = 1
                               AND t2.sen <= t1.sen
                     GROUP BY t1.sen
                    ) AS factor
                ON factor.sen = t1.sen
         WHERE  FCode = 81
                AND DCode = 1
         GROUP BY t1.sen
        ) AS X ;

Answer 4

您应当能够按照这一一般模式,将分顺序置于条款之下:

SELECT TABLE1.ID, SUM(TABLE1.A), ROUND(SUM(T2.B1), 2)
FROM TABLE1, (SELECT SUM(B) B1 FROM TABLE2 WHERE ...) T2
GROUP BY TABLE1.ID

回答你的询问,你可能会有类似的情况:

select 
    t1.sen, 
    sum(t1.d1)as d1, 
    sum(t1.d2)as d2, 
    sum(t1.d1+t1.d2) as d_sum,
    Round((sum((1000*(t1.d1+t1.d2))/(9500-(
        t2a.s
    )))),1) as SSS 
from 
    tbl1 t1,
    (
       select sum(t2.t_shab+t2.t_rooz) s
       from tbl1 t2 
       where FCode=81 AND DCode=1 AND t2.sen<=t1.sen
    ) t2a
where 
    FCode = 81 
    AND DCode = 1
group by
    t1.sen

友情链接