select [time],ID,ChargeMoney=sum(ChargeMoney), ConsumeMoney=sum(ConsumeMoney),ReturnMoney=sum(ReturnMoney) from ( select [time]=ChargeTime,ID,ChargeMoney=sum(ChargeMoney),ConsumeMoney=0,ReturnMoney=0 from ChargeMonay_Records group by ChargeTime,ID union all select [time]=ConsumeTime,ID,ChargeMoney=0,ConsumeMoney=sum(ConsumeMoney),ReturnMoney=0 from ConsumeMoney_Records group by ConsumeTime,ID union all select [time]=ReturnTime,ID,ChargeMoney=0,ConsumeMoney=0,ReturnMoney=sum(ReturnMoney) from ReturnMoney_Records group by ReturnTime,ID ) t group by [time],ID
select cast(round(Time) as datetime) as Time, ID, isnull(sum(ChargeMoney), 0.0) as ChargeMoney, isnull(sum(ConsumeMoney), 0.0) as ConsumeMoney, isnull(sum(ReturnMoney), 0.0) as ReturnMoney from (select ChargeTime as Time, ID, ChargeMoney, 0.0 as ConsumeMoney, 0.0 as ReturnMoney from ChargeMonay_Records union select ConsumeTime as Time, ID, 0.0 as ChargeMoney, ConsumeMoney, 0.0 as ReturnMoney from ConsumeMoney_Records union select ReturnTime as Time, ID, 0.0 as ChargeMoney, 0.0 as ConsumeMoney, ReturnMoney from ReturnMoney_Records) a group by round(Time), ID
你的日期需要转换一下,join 的字段是id.
select cast(round(cast(Time as float), 0) as datetime) as Time, ID, isnull(sum(ChargeMoney), 0.0) as ChargeMoney, isnull(sum(ConsumeMoney), 0.0) as ConsumeMoney, isnull(sum(ReturnMoney), 0.0) as ReturnMoney from (select ChargeTime as Time, ID, ChargeMoney, 0.0 as ConsumeMoney, 0.0 as ReturnMoney from ChargeMonay_Records union select ConsumeTime as Time, ID, 0.0 as ChargeMoney, ConsumeMoney, 0.0 as ReturnMoney from ConsumeMoney_Records union select ReturnTime as Time, ID, 0.0 as ChargeMoney, 0.0 as ConsumeMoney, ReturnMoney from ReturnMoney_Records) a group by round(cast(Time as float), 0), ID
写个笨笨的办法: select tt.time , tt.id , sum(tt.chargemoney) as chargemoeny, sum(tt.consumemoney) as consumemoney ,sum(returnmoney) as returnmoney from (select a.consumetime as time, a.id, sum(a.chargemoney) as chargemoney ,0 as consumemoney,0 as returnmoney from a group by a.consumetime as time, a.id union all select b.consumetime as time ,b.id , 0 as chargemoney ,sum(consumemoney) as consumemoney ,0 as returnmoney from b group by b.consumetime as time ,b.id union all select c.consumetime as time,c.id,0 as chargemoney ,0 as consumemoney,sum(returnmoney) as returnmoney from c group by c.consumetiem,c.id ) tt group by tt.time,tt.id
可能会有一些小问题,大概就是这样了,下班了,闪人!!! select cast(round(cast(Time as float), 0) as datetime) as Time, ID, isnull(sum(ChargeMoney), 0.0) as ChargeMoney, isnull(sum(ConsumeMoney), 0.0) as ConsumeMoney, isnull(sum(ReturnMoney), 0.0) as ReturnMoney from (select ChargeTime as Time, ID, ChargeMoney, 0.0 as ConsumeMoney, 0.0 as ReturnMoney from ChargeMonay_Records union select ConsumeTime as Time, ID, 0.0 as ChargeMoney, ConsumeMoney, 0.0 as ReturnMoney from ConsumeMoney_Records union select ReturnTime as Time, ID, 0.0 as ChargeMoney, 0.0 as ConsumeMoney, ReturnMoney from ReturnMoney_Records) a group by round(cast(Time as float), 0), ID
向 dulei115() 学习。把time 用convert转一下: select Time, ID, isnull(sum(ChargeMoney), 0.0) as ChargeMoney, isnull(sum(ConsumeMoney), 0.0) as ConsumeMoney, isnull(sum(ReturnMoney), 0.0) as ReturnMoney from (select convert(char(10) ,ChargeTime) as Time, ID, ChargeMoney, 0.0 as ConsumeMoney, 0.0 as ReturnMoney from ChargeMonay_Records union select convert(char(10) ,ConsumeTime) as Time, ID, 0.0 as ChargeMoney, ConsumeMoney, 0.0 as ReturnMoney from ConsumeMoney_Records union select convert(char(10) ,ReturnTime) as Time, ID, 0.0 as ChargeMoney, 0.0 as ConsumeMoney, ReturnMoney from ReturnMoney_Records) a group by round(Time), ID order by Time 是不是会好些
select * from Table1 a left join Table2 b on a.ChargeTime=b.ChargeTime and a.ID=b.ID left join Table3 c on a.ChargeTime=c.ChargeTime and a.ID=c.ID
借用pbsql(风云) 的改下:select [time],ID,ChargeMoney=sum(ChargeMoney), ConsumeMoney=sum(ConsumeMoney),ReturnMoney=sum(ReturnMoney) from ( select [time]=convert(varchar(10),ChargeTime,120),ID,ChargeMoney=sum(ChargeMoney),ConsumeMoney=0,ReturnMoney=0 from ChargeMonay_Records group by convert(varchar(10),ChargeTime,120),ID union all select [time]=convert(varchar(10),ConsumeTime,120),ID,ChargeMoney=0,ConsumeMoney=sum(ConsumeMoney),ReturnMoney=0 from ConsumeMoney_Records group by convert(varchar(10),ConsumeTime,120),ID union all select [time]=convert(varchar(10),ConsumeTime,120),ID,ChargeMoney=0,ConsumeMoney=0,ReturnMoney=sum(ReturnMoney) from ReturnMoney_Records group by convert(varchar(10),ConsumeTime,120),ID ) t group by [time],ID
也可以这样写:select b.[time],b.[ID],isnull(c.ChargeMoney,0.0) as ChargeMoney,isnull(d.ConsumeMoney,0.0) as ConsumeMoney,isnull(e.ReturnMoney,0.0) as ReturnMoney from (select a.[time],a.[ID] from ( select convert(varchar(10),ChargeTime,120) as [time],[ID] from ChargeMonay_Records group by convert(varchar(10),ChargeTime,120),[ID] union all select convert(varchar(10),ConsumeTime,120) as [time],[ID] from ConsumeMoney_Records group by convert(varchar(10),ConsumeTime,120),[ID] union all select convert(varchar(10),ConsumeTime,120) as [time],[ID] from ReturnMoney_Records group by convert(varchar(10),ConsumeTime,120),[ID]) as a group by a.[time],a.[ID] order by a.[time],a.[ID]) as b left join (select convert(varchar(10),ChargeTime,120) as [time],[ID],sum(ChargeMoney) as ChargeMoney from ChargeMonay_Records group by convert(varchar(10),ChargeTime,120),[ID]) as c on b.[time]=c.[time] and b.[ID]=c.[ID] left join (select convert(varchar(10),ConsumeTime,120) as [time],[ID],sum(ConsumeMoney) as ConsumeMoney from ConsumeMoney_Records group by convert(varchar(10),ConsumeTime,120),[ID]) as d on b.[time]=d.[time] and b.[ID]=d.[ID] left join (select convert(varchar(10),ConsumeTime,120) as [time],[ID],sum(ReturnMoney) as ReturnMoney from ReturnMoney_Records group by convert(varchar(10),ConsumeTime,120),[ID]) as e on b.[time]=e.[time] and b.[ID]=e.[ID]
ConsumeMoney=sum(ConsumeMoney),ReturnMoney=sum(ReturnMoney)
from
(
select [time]=ChargeTime,ID,ChargeMoney=sum(ChargeMoney),ConsumeMoney=0,ReturnMoney=0
from ChargeMonay_Records group by ChargeTime,ID
union all
select [time]=ConsumeTime,ID,ChargeMoney=0,ConsumeMoney=sum(ConsumeMoney),ReturnMoney=0
from ConsumeMoney_Records group by ConsumeTime,ID
union all
select [time]=ReturnTime,ID,ChargeMoney=0,ConsumeMoney=0,ReturnMoney=sum(ReturnMoney)
from ReturnMoney_Records group by ReturnTime,ID
) t group by [time],ID
isnull(sum(ChargeMoney), 0.0) as ChargeMoney,
isnull(sum(ConsumeMoney), 0.0) as ConsumeMoney,
isnull(sum(ReturnMoney), 0.0) as ReturnMoney
from
(select ChargeTime as Time, ID, ChargeMoney, 0.0 as ConsumeMoney, 0.0 as ReturnMoney
from ChargeMonay_Records
union
select ConsumeTime as Time, ID, 0.0 as ChargeMoney, ConsumeMoney, 0.0 as ReturnMoney
from ConsumeMoney_Records
union
select ReturnTime as Time, ID, 0.0 as ChargeMoney, 0.0 as ConsumeMoney, ReturnMoney
from ReturnMoney_Records) a
group by round(Time), ID
isnull(sum(ChargeMoney), 0.0) as ChargeMoney,
isnull(sum(ConsumeMoney), 0.0) as ConsumeMoney,
isnull(sum(ReturnMoney), 0.0) as ReturnMoney
from
(select ChargeTime as Time, ID, ChargeMoney, 0.0 as ConsumeMoney, 0.0 as ReturnMoney
from ChargeMonay_Records
union
select ConsumeTime as Time, ID, 0.0 as ChargeMoney, ConsumeMoney, 0.0 as ReturnMoney
from ConsumeMoney_Records
union
select ReturnTime as Time, ID, 0.0 as ChargeMoney, 0.0 as ConsumeMoney, ReturnMoney
from ReturnMoney_Records) a
group by round(cast(Time as float), 0), ID
select tt.time , tt.id , sum(tt.chargemoney) as chargemoeny, sum(tt.consumemoney) as consumemoney ,sum(returnmoney) as returnmoney
from
(select a.consumetime as time, a.id, sum(a.chargemoney) as chargemoney ,0 as consumemoney,0 as returnmoney
from a
group by a.consumetime as time, a.id
union all
select b.consumetime as time ,b.id , 0 as chargemoney ,sum(consumemoney) as consumemoney ,0 as returnmoney
from b
group by
b.consumetime as time ,b.id
union all
select c.consumetime as time,c.id,0 as chargemoney ,0 as consumemoney,sum(returnmoney) as returnmoney
from c
group by
c.consumetiem,c.id
) tt
group by tt.time,tt.id
select cast(round(cast(Time as float), 0) as datetime) as Time, ID,
isnull(sum(ChargeMoney), 0.0) as ChargeMoney,
isnull(sum(ConsumeMoney), 0.0) as ConsumeMoney,
isnull(sum(ReturnMoney), 0.0) as ReturnMoney
from
(select ChargeTime as Time, ID, ChargeMoney, 0.0 as ConsumeMoney, 0.0 as ReturnMoney
from ChargeMonay_Records
union
select ConsumeTime as Time, ID, 0.0 as ChargeMoney, ConsumeMoney, 0.0 as ReturnMoney
from ConsumeMoney_Records
union
select ReturnTime as Time, ID, 0.0 as ChargeMoney, 0.0 as ConsumeMoney, ReturnMoney
from ReturnMoney_Records) a
group by round(cast(Time as float), 0), ID
select Time, ID,
isnull(sum(ChargeMoney), 0.0) as ChargeMoney,
isnull(sum(ConsumeMoney), 0.0) as ConsumeMoney,
isnull(sum(ReturnMoney), 0.0) as ReturnMoney
from
(select convert(char(10) ,ChargeTime) as Time, ID, ChargeMoney, 0.0 as ConsumeMoney, 0.0 as ReturnMoney
from ChargeMonay_Records
union
select convert(char(10) ,ConsumeTime) as Time, ID, 0.0 as ChargeMoney, ConsumeMoney, 0.0 as ReturnMoney
from ConsumeMoney_Records
union
select convert(char(10) ,ReturnTime) as Time, ID, 0.0 as ChargeMoney, 0.0 as ConsumeMoney, ReturnMoney
from ReturnMoney_Records) a
group by round(Time), ID
order by Time
是不是会好些
from Table1 a
left join Table2 b on a.ChargeTime=b.ChargeTime and a.ID=b.ID
left join Table3 c on a.ChargeTime=c.ChargeTime and a.ID=c.ID
ConsumeMoney=sum(ConsumeMoney),ReturnMoney=sum(ReturnMoney)
from
(
select [time]=convert(varchar(10),ChargeTime,120),ID,ChargeMoney=sum(ChargeMoney),ConsumeMoney=0,ReturnMoney=0
from ChargeMonay_Records group by convert(varchar(10),ChargeTime,120),ID
union all
select [time]=convert(varchar(10),ConsumeTime,120),ID,ChargeMoney=0,ConsumeMoney=sum(ConsumeMoney),ReturnMoney=0
from ConsumeMoney_Records group by convert(varchar(10),ConsumeTime,120),ID
union all
select [time]=convert(varchar(10),ConsumeTime,120),ID,ChargeMoney=0,ConsumeMoney=0,ReturnMoney=sum(ReturnMoney)
from ReturnMoney_Records group by convert(varchar(10),ConsumeTime,120),ID
) t group by [time],ID
(select a.[time],a.[ID] from (
select convert(varchar(10),ChargeTime,120) as [time],[ID] from ChargeMonay_Records group by convert(varchar(10),ChargeTime,120),[ID]
union all
select convert(varchar(10),ConsumeTime,120) as [time],[ID] from ConsumeMoney_Records group by convert(varchar(10),ConsumeTime,120),[ID]
union all
select convert(varchar(10),ConsumeTime,120) as [time],[ID] from ReturnMoney_Records group by convert(varchar(10),ConsumeTime,120),[ID]) as a group by a.[time],a.[ID] order by a.[time],a.[ID]) as b
left join (select convert(varchar(10),ChargeTime,120) as [time],[ID],sum(ChargeMoney) as ChargeMoney from ChargeMonay_Records group by convert(varchar(10),ChargeTime,120),[ID]) as c on b.[time]=c.[time] and b.[ID]=c.[ID]
left join (select convert(varchar(10),ConsumeTime,120) as [time],[ID],sum(ConsumeMoney) as ConsumeMoney from ConsumeMoney_Records group by convert(varchar(10),ConsumeTime,120),[ID]) as d on b.[time]=d.[time] and b.[ID]=d.[ID]
left join (select convert(varchar(10),ConsumeTime,120) as [time],[ID],sum(ReturnMoney) as ReturnMoney from ReturnMoney_Records group by convert(varchar(10),ConsumeTime,120),[ID]) as e on b.[time]=e.[time] and b.[ID]=e.[ID]