select
Rec_DT.
[次数]=sum(case when [type]=1 then 1 else 0 end),
[金额]=sum(case when [type]=2 then Charge else 0 end)
from
(
select *,[type]=1 from A
union all
select *,2 from B
) t
group by Rec_DT
order by Rec_DT
Rec_DT.
[次数]=sum(case when [type]=1 then 1 else 0 end),
[金额]=sum(case when [type]=2 then Charge else 0 end)
from
(
select *,[type]=1 from A
union all
select *,2 from B
) t
group by Rec_DT
order by Rec_DT
[Rec_DT]=replace(convert(char(10),Rec_DT,120),'-','/'),
[次数]=sum(case when [type]=1 then 1 else 0 end),
[金额]=sum(case when [type]=2 then Charge else 0 end)
from
(
select *,[type]=1 from A
union all
select *,2 from B
) t
group by replace(convert(char(10),Rec_DT,120),'-','/')
order by replace(convert(char(10),Rec_DT,120),'-','/')
[Rec_DT]=convert(char(10),Rec_DT,120),
[次数]=sum(case when [type]=1 then 1 else 0 end),
[金额]=sum(case when [type]=2 then Charge else 0 end)
from
(
select *,[type]=1 from A
union all
select *,2 from B
) t
group by convert(char(10),Rec_DT,120)
order by convert(char(10),Rec_DT,120)
select convert(varchar(10),DATEADD(day,c.id,'2005-06-30'),121) as 日期,count(a.ID) as 次数,sum(isnull(b.Charge,0)) as 金额
from #1 c left join A on convert(varchar(10),DATEADD(day,c.id,'2005-06-30'),121)=convert(varchar(10),a.Rec_DT,121) left join b
on convert(varchar(10),DATEADD(day,c.id,'2005-06-30'),121)=convert(varchar(10),b.ChargeDT,121) group by convert(varchar(10),DATEADD(day,c.id,'2005-06-30'),121)
order by convert(varchar(10),DATEADD(day,c.id,'2005-06-30'),121)
create table ta(id varchar(10), Rec_DT datetime, memo varchar(10))
create table tb(id varchar(10), chargeDT datetime, charge int)
insert ta select '001', '2005/7/8 16:11:00', 'a'
union all select '001', '2005/7/8 15:11:00', 'bb'
union all select '002', '2005/7/8 16:11:00', 'ccc'
union all select '003', '2005/7/10 16:11:00', 'dd'
insert tb select '001', '2005/7/8 15:11:00', 100
union all select '004', '2005/7/8 16:11:00', 200
union all select '005', '2005/7/9 16:11:00', 100
--查询
select 时间=isnull(t1.days,t2.days), 次数=isnull(times_sum,0), 金额=isnull(money_sum, 0)
from (select days=convert(varchar(10), Rec_DT, 120), times_sum=count(*)
from ta
group by convert(varchar(10), Rec_DT, 120))t1
full join
(select days=convert(varchar(10), chargedt, 120), money_sum=sum(charge)
from tb
group by convert(varchar(10), chargedt, 120))t2
on t1.days=t2.days
--清除
drop table ta
drop table tb
--楼主将查询语句改成下面的就可以了。(其实就是将120改成111而已)
--查询
select 时间=isnull(t1.days,t2.days), 次数=isnull(times_sum,0), 金额=isnull(money_sum, 0)
from (select days=convert(varchar(10), Rec_DT, 111), times_sum=count(*)
from ta
group by convert(varchar(10), Rec_DT, 111))t1
full join
(select days=convert(varchar(10), chargedt, 111), money_sum=sum(charge)
from tb
group by convert(varchar(10), chargedt, 111))t2
on t1.days=t2.days