记录合计

[sunhood]

table T 如下
pix  id code amount Amount1 weight  cost tool
 1   1  807   8        2     4.2    200   X1
 2   2  807                  8.4    400   X2
 1   3  809   3        4     2.2    150   X1
 2   4  809                  8.8    600   X4
 3   5  809                               X2
 1   6  810   6        3     3.2    160   X3
 2   7  810                  9.6    480   X4
 3   8  810                               X2
...
要得到如下结果：
Amount1 Weight  Cost
  9      26.8   1480

结果中的数据来源如下：
Amount1 = sum(T.Amount1)
Weight = 8.4+ 8.8+ 9.6 ，也就是相同T.code的最大T.weight只和，其中：大T.weight = T.Amount1 * 小T.Weight
cost = 取相同T.Code的最大T.Cost之和，数据来源与Weight雷同

不知道说清楚没有，请大侠伸手

[jiang5311]

[Quote=引用 2 楼 Haiwer 的回复:]
上面错了，更正如下：


SQL codeselect sum(Amount1) as Amount1,
sum(weight) as weight,
sum(cost) as cost
from (
select Amount1,
case when not exists (
select 1 from T
where code=a.code
and weight>a.weight
) then weight
else 0
end as weight,
case when not exists (
sele…
[/Quote]
厉害

[sunhood]

高手，解决问题了，谢谢啊

[昵称被占用了]

select sum(Amount1) as Amount1,
       sum(case when not exists (
            select 1 from T
            where code=a.code
            and weight>a.weight
            ) then weight 
            else 0 
            end) as weight,
       sum(case when not exists (
            select 1 from T
            where code=a.code
            and cost>a.cost
            ) then cost
            else 0 
            end) as cost
from T a            

[昵称被占用了]

上面错了，更正如下：

select sum(Amount1) as Amount1,
       sum(weight) as weight,
       sum(cost) as cost
from (
select Amount1,
       case when not exists (
            select 1 from T
            where code=a.code
            and weight>a.weight
            ) then weight 
            else 0 
            end as weight,
       case when not exists (
            select 1 from T
            where code=a.code
            and cost>a.cost
            ) then cost
            else 0 
            end as cost
from T a            
) as temp