SQL：如何在按部分分组的查询中使用子查询？

Question

如何在按部分分组的查询中使用子查询？

我使用 SQL Server 2008 R2 和 Delphi 2010

我收到此错误：

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.森

Answer 1

没有任何保证，您可以尝试，如果不起作用请告诉我，我将删除我的答案

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;

纪念品：

• 添加了函数创建

，它不会让我真正修复代码，因为我的编辑太短了。所以我不得不写一些更多的垃圾，这样代码修复才能被接受。

Answer 2

试试这个：

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 3

您应该能够将子查询放在 FROM 子句下，遵循以下一般模式：

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

Answer 4

这是真正的方法

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;