select
Time = convert(char(10),Time,120),
out = sum(case when type = '发货' then ProductPay else 0 end),
OutCarry = sum(case when type = '发货' then CarryPay else 0 end),
in = sum(case when type = '收货' then ProductPay else 0 end),
InCarry = sum(case when type = '收货' then CarryPay else 0 end)
from
ORDER
group by
convert(char(10),Time,120)
Time = convert(char(10),Time,120),
out = sum(case when type = '发货' then ProductPay else 0 end),
OutCarry = sum(case when type = '发货' then CarryPay else 0 end),
in = sum(case when type = '收货' then ProductPay else 0 end),
InCarry = sum(case when type = '收货' then CarryPay else 0 end)
from
ORDER
group by
convert(char(10),Time,120)
Create Table 表(ID varchar(10),ProductPay integer ,CarryPay integer,Type varchar(10),[time] datetime)
--插入数据
insert into 表
select '1','200','20','发货','2005-1-1 11:52:23' union
select '2','200','50','发货','2005-1-1 11:56:23' union
select '3','150','12','发货','2005-1-2 13:14:56' union
select '4','620','45','收货','2005-1-1 12:25:02' union
select '5','120','10','收货','2005-1-3 22:12:30'
select * from 表
--测试语句
select convert(char(10),time,120),
sum(case when type='发货' then productpay else 0 end)[发货总金额],
sum(case when type='发货' then CarryPay else 0 end)[发货总运费],
sum(case when type='收货' then productpay else 0 end)[收货总金额],
sum(case when type='收货' then CarryPay else 0 end)[收货总运费]
from 表
group by convert(char(10),time,120)
--删除测试环境
Drop Table 表
select convert(char(10),time,120) [time],
sum(case when type='发货' then productpay else 0 end)[发货总金额],
sum(case when type='发货' then CarryPay else 0 end)[发货总运费],
sum(case when type='收货' then productpay else 0 end)[收货总金额],
sum(case when type='收货' then CarryPay else 0 end)[收货总运费]
from 表
group by convert(char(10),time,120)
--插入数据
insert into 表
select '1','200','20','发货','2005-1-1 11:52:23' union
select '2','200','50','发货','2005-1-1 11:56:23' union
select '3','150','12','发货','2005-1-2 13:14:56' union
select '4','620','45','收货','2005-1-1 12:25:02' union
select '5','120','10','收货','2005-1-3 22:12:30'
select * from 表
--测试语句
select convert(char(10),time,120),
sum(case when type='发货' then productpay else 0 end)[发货总金额],
sum(case when type='发货' then CarryPay else 0 end)[发货总运费],
sum(case when type='发货' then 1 else 0 end)[发货次数],
sum(case when type='收货' then productpay else 0 end)[收货总金额],
sum(case when type='收货' then CarryPay else 0 end)[收货总运费],
sum(case when type='收货' then 1 else 0 end)[收货次数]
from 表
group by convert(char(10),time,120)
--删除测试环境
Drop Table 表