SQL：我如何在一个分组的查询中使用子查询？ [英] SQL : How can I use sub query in a query with group by section?

问题描述

我使用SQL Server 2008 R2和Delphi 2010

我收到此错误：

 不能对表达式
执行包含汇总的汇总函数或子查询。 
  

像这样的查询：

 选择
 t1.sen，
 sum（t1.d1）as d1，
 sum（t1.d2）as d2，
 sum（t1。 （（sum（（1000 *（t1.d1 + t1.d2））/（9500-（
）选择总和（t2.t_shab + t2.t_rooz（d1 + t1.d2）as d_sum，
 Round ）
 from tbl1 t2 
 where FCode = 81 AND DCode = 1 AND t2.sen <= t1.sen 
）））），1）as $ S $ 
 from 
 tbl1 t1 
其中
 FCode = 81 
 AND DCode = 1 
  

group by
t1.sen

解决方案

其真正的方法

  create function getSumBSen2（@pfcode INT，@pdcode INT，@pSen INT）返回int 
作为
 begin 
 declare @r int ; 
 select 
 @ r = sum（t2.t_shab + t2.t_rooz）
 from 
 tbl1 t2 
 where 
 t2.FCode = @pfcode 
和t2.DCode = @pdcode 
和t2.sen <= @pSen; 
 
 return（@r）; 
 end; 
 GO 
 
选择
 t1.sen，
 sum（t1.d1）as d1，
 sum（t1.d2）as d2，$ b $（sum（（1000 *（t1.d1 + t1.d2）+0.01）/（9500-（dbo.getSumBSen2（t1.d1 + t1.d2））作为d_sum，
 Round FCode，t1.DCode，t1.sen））））），1）作为SSS 
从
 tbl1 t1 
其中
 t1.FCode = 81 
和t1 .DCode = 1 
 group by 
 t1.sen; 
  

How can I use sub query in a query with group by section?

I use SQL Server 2008 R2 AND Delphi 2010

I receive this error:

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

Like this 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

解决方案

Its the true way

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;

这篇关于SQL：我如何在一个分组的查询中使用子查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！