ps_no ps_dd prd_no cus_no dep amtn
001 207-5-5 111 01 025 10
001 207-5-5 112 01 025 20
001 207-5-5 113 01 025 30
002 207-5-8 110 03 065 18
002 207-5-8 112 03 065 12
003 207-5-8 115 06 035 10
004 207-5-8 119 05 025 55
004 207-5-9 120 05 025 22
005 207-5-9 121 08 065 16
...
------------------------------
注:(ps_no 单号,ps_dd 日期,prd_no货品编号,cus_no供应商编号,dep部门号,amtn金额)现在我要统计出这样的结果:
001 01 025 70
002 03 065 30
003 06 035 10
004 05 025 77
005 08 065 16
...
--------------
先解决这个看看
本来是有四个条件的,即根据公司,部门,供应商,和货品类别来统计得到上面的结果
001 207-5-5 111 01 025 10
001 207-5-5 112 01 025 20
001 207-5-5 113 01 025 30
002 207-5-8 110 03 065 18
002 207-5-8 112 03 065 12
003 207-5-8 115 06 035 10
004 207-5-8 119 05 025 55
004 207-5-9 120 05 025 22
005 207-5-9 121 08 065 16
...
------------------------------
注:(ps_no 单号,ps_dd 日期,prd_no货品编号,cus_no供应商编号,dep部门号,amtn金额)现在我要统计出这样的结果:
001 01 025 70
002 03 065 30
003 06 035 10
004 05 025 77
005 08 065 16
...
--------------
先解决这个看看
本来是有四个条件的,即根据公司,部门,供应商,和货品类别来统计得到上面的结果
from tb
group by ps_no,cus_no,dep
order by ps_no
select ps_no,cus_no,dep,sum(amtn)amtn from tb
group by ps_no,cus_no,dep
go
insert into tb
select '001', '207-5-5', 111, '01', '025', 10
union all select
'001', '207-5-5', 112, '01', '025', 20
union all select
'001', '207-5-5', 113, '01', '025', 30
union all select
'002', '207-5-8', 110, '03', '065', 18
union all select
'002', '207-5-8', 112, '03', '065', 12
union all select
'003', '207-5-8', 115, '06', '035', 10
union all select
'004', '207-5-8', 119, '05', '025', 55
union all select
'004', '207-5-9', 120, '05', '025', 22
union all select
'005', '207-5-9', 121, '08', '065', 16
-- drop table tb
select ps_no,cus_no,dep,sum(isnull(amtn,0)) as amtn from tb group by dep,cus_no,ps_no order by ps_no
------------
001 01 025 60
002 03 065 30
003 06 035 10
004 05 025 77
005 08 065 16
insert into tb values('001', '2007-5-5', 111 , '01', '025', 10 )
insert into tb values('001', '2007-5-5', 112 , '01', '025', 20 )
insert into tb values('001', '2007-5-5', 113 , '01', '025', 30 )
insert into tb values('002', '2007-5-8', 110 , '03', '065', 18 )
insert into tb values('002', '2007-5-8', 112 , '03', '065', 12 )
insert into tb values('003', '2007-5-8', 115 , '06', '035', 10 )
insert into tb values('004', '2007-5-8', 119 , '05', '025', 55 )
insert into tb values('004', '2007-5-9', 120 , '05', '025', 22 )
insert into tb values('005', '2007-5-9', 121 , '08', '065', 16 )
goselect ps_no,cus_no,dep, sum(amtn) amtn from tb group by ps_no,cus_no,depdrop table tb/*
ps_no cus_no dep amtn
---------- ---------- ---------- -----------
001 01 025 60
002 03 065 30
003 06 035 10
004 05 025 77
005 08 065 16(所影响的行数为 5 行)
*/
group by ps_no,cus_no,dep
from tb
group by ps_no,cus_no,dep
order by ps_no