select time,out=isnull((select ProductPay from order where time=a.time and type='发货'),0), OutCarry=isnull((select carryPay from order where time=a.time and type='发货'),0),in=isnull((select ProductPay from order where time=a.time and type='收货'),0), inCarry=isnull((select carryPay from order where time=a.time and type='收货'),0) from order a group by time
(select convert(varchar(10),time,120) as time,sum(out) as out,sum(outcarry) as outcarry from order group by convert(varchar(10),time,120) ) a
full join
(select convert(varchar(10),time,120) as time,sum(in) as in,sum(incarry) as incarry from order group by convert(varchar(10),time,120) ) b
on a.time=b.time
(select convert(varchar(10),time,120) as time,sum(productpay) as out,sum(carrypay) as outcarry from order where type='发货' group by convert(varchar(10),time,120) ) a
full join
(select convert(varchar(10),time,120) as time,sum(productpay) as in,sum(carrypay) as incarry from order where type='收货' group by convert(varchar(10),time,120) ) b
on a.time=b.time
Create Table [Order] (ID int, ProductPay int,CarryPay int, Type varchar(5), Time datetime)
insert into [Order]
select 1, 200 , 20 , '發貨', '2005-1-1 11:52:23' union all
select 2, 150 , 12 , '發貨' ,'2005-1-2 13:14:56' union all
select 3 , 620 , 45 , '收貨', '2005-1-1 12:25:02' union all
select 4 , 120 , 10 , '收貨' ,'2005-1-3 22:12:30'
----------------------實現語句-----------------------------------
select convert(varchar(10),time,120),out=isnull((select ProductPay from [order] where
convert(varchar(10),time,120)=convert(varchar(10),a.time,120) and type='發貨'),0),
OutCarry=isnull((select carryPay from [order] where convert(varchar(10),time,120)
=convert(varchar(10),a.time,120) and type='發貨'),0),[in]=isnull((select ProductPay from [order] where convert(varchar(10),time,120)
=convert(varchar(10),a.time,120) and type='收貨'),0), inCarry=isnull((select carryPay from [order]
where convert(varchar(10),time,120)=convert(varchar(10),a.time,120) and type='收貨'),0) from [order] a group by convert(varchar(10),time,120)
--------------------------結果--------------------------
Time Out(发货金额)OutCarry(发货运费)In(收货金额)InCarry(收获运费)
----------------------------------------
2005-1-1 200 20 620 45
2005-1-2 150 12 0 0
2005-1-3 0 0 120 10
请帮忙。