select Atime=convert(varchar(10),isnull(Atime,FAtime),120),
SAmoney=sum(case Atype when '收款' then Amoney else 0 end),
FAmoney=sum(case Atype when '发款' then Amoney else 0 end)
from A group by convert(varchar(10),isnull(Atime,FAtime),120)既然有Atype,表结构就不应该弄两个时间,以个就够了
SAmoney=sum(case Atype when '收款' then Amoney else 0 end),
FAmoney=sum(case Atype when '发款' then Amoney else 0 end)
from A group by convert(varchar(10),isnull(Atime,FAtime),120)既然有Atype,表结构就不应该弄两个时间,以个就够了
case
when SAtime is not null then convert(char(10),SAtime,120)
else convert(char(10),FAtime,120)
end as Atime,
sum(case SAtime is not null then Amoney else 0 end) as SAmoney,
sum(case FAtime is not null then Amoney else 0 end) as FAmoney
from
A
group by
case
when SAtime is not null then convert(char(10),SAtime,120)
else convert(char(10),FAtime,120)
end
SAmoney=sum(case when Atype='收款' then Amoney else 0 end),
FAmoney=sum(case when Atype='发款' then Amoney else 0 end)
from 表A
group by convert(varchar(10),Atime,120)
from(
select dt=convert(char(10),SAtime,120),SAmoney=Amoney,FAmoney=0
from 表 where Atype=N'收款'
union all
select dt=convert(char(10),FAtime,120),SAmoney=0,FAmoney=Amoney
from 表 where Atype=N'发款'
)a group by dt
samoney=sum(select amoney from a where atype='收款' and convert(char(10),satime,120)=convert(char(10),t.satime,120),
famoney=sum(select amoney from a where atype='发款' and convert(char(10),fatime,120)=convert(char(10),t.fatime,120)
from a t
order by atime asc
union all
select convert(varchar(10),FATime,120),0,sum(Amoney) from test where Atype= '发款' group by convert(varchar(10),FATime,120)
order by Atime
when '收款' then datename(yyyy,SAtime)+'-'+datename(mm,SAtime)+'-'+datename(dd,SAtime)
when '发款' then datename(yyyy,FAtime)+'-'+datename(mm,FAtime)+'-'+datename(dd,FAtime)
end
,SAmoney=sum(isnull(case Atype when '收款' then Amoney end,0)),
FAmoney=sum(isnull(case Atype when '发款' then Amoney end,0))
from 表名
group by case Atype
when '收款' then datename(yyyy,SAtime)+'-'+datename(mm,SAtime)+'-'+datename(dd,SAtime)
when '发款' then datename(yyyy,FAtime)+'-'+datename(mm,FAtime)+'-'+datename(dd,FAtime)
end
case 现在状态
when '新订单' then convert(char(10),生成时间,120)
when '已收款' then convert(char(10),收款时间,120)
when '已发货' then convert(char(10),发货时间,120)
end as 时间,
sum(case 现在状态 when '新订单' then 金额 else 0 end) as 当天新订单金额,
sum(case 现在状态 when '已收款' then 金额 else 0 end) as 当天收款订单总金额,
sum(case 现在状态 when '已发货' then 金额 else 0 end) as 当天发货订单总金额
from
购物订单表
group by
case 现在状态
when '新订单' then convert(char(10),生成时间,120)
when '已收款' then convert(char(10),收款时间,120)
when '已发货' then convert(char(10),发货时间,120)
endorselect
a.时间,
sum(a.新订单金额) as 当天新订单金额,
sum(a.已收款金额) as 当天收款订单总金额,
sum(a.已发货金额) as 当天发货订单总金额
from
(select
case 现在状态
when '新订单' then convert(char(10),生成时间,120)
when '已收款' then convert(char(10),收款时间,120)
when '已发货' then convert(char(10),发货时间,120)
end as 时间,
case 现在状态 when '新订单' then 金额 else 0 end as 新订单金额,
case 现在状态 when '已收款' then 金额 else 0 end as 已收款金额,
case 现在状态 when '已发货' then 金额 else 0 end as 已发货金额
from
购物订单表) a
group by
a.时间
from(
select dt=convert(char(10),SAtime,120),SAmoney=Amoney,FAmoney=0
from 表 where Atype=N'收款'
union all
select dt=convert(char(10),FAtime,120),SAmoney=0,FAmoney=Amoney
from 表 where Atype=N'发款'
)a group by dt
你的代码执行正常:
select
a.时间,
sum(a.新订单金额) as 当天新订单金额,
sum(a.已收款金额) as 当天收款订单总金额,
sum(a.已发货金额) as 当天发货订单总金额
from
(select
case 现在状态
when '新订单' then convert(char(10),生成时间,120)
when '已收款' then convert(char(10),收款时间,120)
when '已发货' then convert(char(10),发货时间,120)
end as 时间,
case 现在状态 when '新订单' then 金额 else 0 end as 新订单金额,
case 现在状态 when '已收款' then 金额 else 0 end as 已收款金额,
case 现在状态 when '已发货' then 金额 else 0 end as 已发货金额
from
购物订单表) a
group by
a.时间
后来我想只判断两种转态 即将
sum(a.已发货金额) as 当天发货订单总金额
when '已发货' then convert(char(10),发货时间,120)
case 现在状态 when '已发货' then 金额 else 0 end as 已发货金额 去掉结果总是第一条记录的时间为null 其他金为0
请问怎样去掉这多余的一条记录呢?
a.时间,
sum(a.新订单金额) as 当天新订单金额,
sum(a.已收款金额) as 当天收款订单总金额
from
(select
case 现在状态
when '新订单' then convert(char(10),生成时间,120)
when '已收款' then convert(char(10),收款时间,120)
end as 时间,
case 现在状态 when '新订单' then 金额 else 0 end as 新订单金额,
case 现在状态 when '已收款' then 金额 else 0 end as 已收款金额
from
购物订单表
where
现在状态 in('新订单','已收款')) a
group by
a.时间