• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

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

fanqingfeng 2005-02-27 04:35:02
订单表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
。。。。


谢谢 急用
...全文
246 点赞 收藏 8
写回复
8 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
fanqingfeng 2005-02-27
zheninchangjiang(我爱燕子) 你的代码不存在错误 但执行结果为空。
回复
fanqingfeng 2005-02-27
Softlee81307(孔腎) 你好 运行的代码中使用您的例子没有问题 当时但运行多数据时有这样的错误

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


请帮忙。
回复
fanqingfeng 2005-02-27
非常感谢两位!!尤其是 Softlee81307(孔腎) ,很快的速度很好的帮我解决了两个大问题,深厚的数据库功底,钦佩!
回复
Softlee81307 2005-02-27
-----------------------建測試數據------------------
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
回复
zheninchangjiang 2005-02-27
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
回复
zheninchangjiang 2005-02-27
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
回复
Softlee81307 2005-02-27
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)
回复
Softlee81307 2005-02-27
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
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2005-02-27 04:35
社区公告
暂无公告