--建表
create table Order_Customer
(
Sorder varchar(7),
Cuscode varchar(3),
Cusname varchar(10),
Stocode varchar(5),
Stoname varchar(10),
SStoquantity int,
SStomoney int
)
insert Order_Customer
select '2006001','001','连锁一店','00001','分割品',1000,50000
union all
select '2006001','001','连锁一店','00002','白条',500,50000
union all
select '2006002','002','连锁二店','00001','分割品',500,250000
union all
select '2006002','002','连锁二店','00002','白条',1500,150000
union all
select '2006002','002','连锁二店','00001','分割品',500,250000
union all
select '2006002','002','连锁二店','00002','白条',1500,150000
--检索所需
select '客户编码'=Cuscode , '客户名称'=Cusname
,'白条数量'= sum(case Stocodewhen 00002 then SStoquantity end)
,'白条金额'=sum(case Stocodewhen 00002 then SStomoney end)
,'分割品数量'=sum(case Stocodewhen 00001 then SStoquantity end)
,'分割品金额'=sum(case Stocodewhen 00001 then SStomoney end)
,'发货数量合计'=sum(case Stocodewhen 00002 then SStoquantity end)+sum(case Stocodewhen 00001 then SStoquantity end)
,'发货金额合计'=sum(case Stocodewhen 00002 then SStomoney end)+sum(case Stocodewhen 00001 then SStomoney end)
from Order_Customer
group by Sorder,Cuscode,Cusname
drop table Order_Customer
|