求SQL语句，计算每天的订单数量。

[fanqingfeng]

订单表ORDER：
ID ProductPay（商品金额）CarryPay（运费） Type Time
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
1 200 20 发货 2005-1-1 11：52：23
2 150 12 发货 2005-1-2 13：14：56
3 620 45 收货 2005-1-1 12：25：02
4 120 10 收货 2005-1-3 22：12：30
。。。


想计算每天发货和收获的金额：
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
。。。。


谢谢 急用

[Softlee81307]

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

[Softlee81307]

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)

[zheninchangjiang]

select isnull(a.time,b.time) as time,a.out,a.outcarry,b.in,b.incarry from
(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

[zheninchangjiang]

select isnull(a.time,b.time) as time,a.out,a.outcarry,b.in,b.incarry from
(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

[Softlee81307]

-----------------------建測試數據------------------
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

[fanqingfeng]

非常感谢两位！！尤其是 Softlee81307(孔腎) ，很快的速度很好的帮我解决了两个大问题，深厚的数据库功底，钦佩！

[fanqingfeng]

Softlee81307(孔腎) 你好 运行的代码中使用您的例子没有问题 当时但运行多数据时有这样的错误

”[Microsoft][ODBC SQL Server Dirver]子查询返回的值多于一个。当子查询跟随在=、！＝、<、<=、>、>=之后，或子查询用作表达式时，这种情况是不容许的“


请帮忙。

[fanqingfeng]

zheninchangjiang(我爱燕子) 你的代码不存在错误 但执行结果为空。