create table tab_a ( gro_id varchar(20), id_no varchar(20), pay_1 dec(10,1), pay_2 dec(10,1)) go insert tab_a select '001','a001',50,105 union all select '001','a002',65,70.2 union all select '001','a003',85.2,58.5 union all select '002','a001',65.0,70.0 union all select '002','a002',75.5,10.5 union all select '002','b001',85.0,70.5 goselect min(gro_id) as gro_id, b.id_no,sum(b.pay_1) as pay_1,sum(b.pay_2) as pay_2 from tab_a b group by b.id_no drop table tab_a /* gro_id id_no pay_1 pay_2 001 a001 115.0 175.0 001 a002 140.5 80.7 001 a003 85.2 58.5 002 b001 85.0 70.5 */
select gro_id, id_no, pay_1=sum(pay_1), pay_2=sum(pay_2) from tab_a group by gro_id,id_no
倒,看错了...select gro_id=min(gro_id), id_no, pay_1=sum(pay_1), pay_2=sum(pay_2) from tab_a group by id_no
/*
gro_id id_no pay_1 pay_2
001 a001 115.0 175.0
001 a002 140.5 80.7
001 a003 85.2 58.5
002 b001 85.0 70.5*/
( gro_id varchar(20),
id_no varchar(20),
pay_1 dec(10,1),
pay_2 dec(10,1))
go
insert tab_a
select '001','a001',50,105 union all
select '001','a002',65,70.2 union all
select '001','a003',85.2,58.5 union all
select '002','a001',65.0,70.0 union all
select '002','a002',75.5,10.5 union all
select '002','b001',85.0,70.5
goselect min(gro_id) as gro_id, b.id_no,sum(b.pay_1) as pay_1,sum(b.pay_2) as pay_2
from tab_a b
group by b.id_no
drop table tab_a
/*
gro_id id_no pay_1 pay_2
001 a001 115.0 175.0
001 a002 140.5 80.7
001 a003 85.2 58.5
002 b001 85.0 70.5
*/
id_no,
pay_1=sum(pay_1),
pay_2=sum(pay_2)
from tab_a
group by gro_id,id_no
id_no,
pay_1=sum(pay_1),
pay_2=sum(pay_2)
from tab_a
group by id_no
select * from tab_a
就搞定这样gro_id id_no pay_1 pay_2
001 a001 115.0 175.0
001 a002 140.5 80.7
001 a003 85.2 58.5
002 b001 85.0 70.5
group by id_nodrop table tab_a