编程论坛 → SQL Server论坛

求助相同数据合并，按条件求和、取数值

yewxj 发布于 2017-08-14 10:55， 2381 次点击

只有本站会员才能查看附件，请登录

求助大神，如图，查询表1里面，A和B相同的、C求和，取数值大的D同行E数据（D可不取，只做判断使用），结果如表2，谢谢！

[此贴子已经被作者于2017-8-14 11:11编辑过]

4 回复

yewxj2017-08-14 12:28

SELECT c.a,c.b,d.e,c.d,c.c FROM 表1 d JOIN (select a.a,a.b,b.d,STR(sum(a.c)) as 'c' from 表1 a INNER JOIN (SELECT a,b,MAX(d) as 'd' FROM 表1 GROUP BY a,b) b on a.a=b.a AND a.b=b.b GROUP BY a.a,a.b,b.d) c on d.a=c.a AND d.b=c.b and d.d=c.d into dbf 表2 GROUP BY c.a,c.b,d.e,c.d,c.d,c.c

自己琢磨出来一个，希望大神能给出更优化的语句。

mywisdom882017-08-15 12:33

select a.行a,a.行b,a.行c,a.行d,b.行e
from (select 行a,行b,sum(行c) as 行c,max(行d) as 行d from 表1 group by 行a,行b)a
left join 表1 b on a.行a=b.行a and a.行b=b.行b and a.行d=b.行d

mywisdom882017-08-15 12:36

上面的没测,应该没问题,标准的SQL2000语句,如果用在VFP9中,需要改改
select a.行a,a.行b,a.行c,a.行d,b.行e ;
from (select 行a,行b,sum(行c) as 行c,max(行d) as 行d from 表1 group by 行a,行b)a ;
left join 表1 b on a.行a=b.行a and a.行b=b.行b and a.行d=b.行d ;
into table d:\表2.dbf

yewxj2017-08-15 14:41

回复 4楼 mywisdom88

谢谢老师！