我有4张表
A
ID        姓名
—————————
1         A1
2         B1
3         C1

B
ID       消费   日期
—————————————
1        100       2016-03-01
3         50       2016-03-02
3         20       2016-03-31
2        12        2016-02-25

C
ID      取现     日期
－－－－－－－－－－
2        30    2016-03-15

D
ID      充值     日期     备注
－－－－－－－－－－－－
1       200     2016-03-15  sys
3       40      2016-03-13   xls
1      100      2016-03-14  sys

我想做查询每个人2016年3月份的消费、取现、充值（要分为2列分别是sys的为一列、xls为一列）在一个报表里显示出来，能再补充下吗？谢谢
要达到的结果
姓名     消费               取现                充值sys     充值xls      
A1        100               0                   300          0
B1         0               30                    0           0
C1        70                0                    0          40

--建立数据
drop table #tmp_a
create table #tmp_a(ID int,姓名 varchar(20))
 insert into #tmp_a(ID,姓名) values(1,'A1')
 insert into #tmp_a(ID,姓名) values(2,'B1')
 insert into #tmp_a(ID,姓名) values(3,'C1')

drop table #tmp_b
create table #tmp_b(ID int,消费 numeric(12,2),日期 datetime)
 insert into #tmp_b(ID,消费,日期) values(1,100,'2016-03-01')
 insert into #tmp_b(ID,消费,日期) values(3,50,'2016-03-02')
 insert into #tmp_b(ID,消费,日期) values(3,20,'2016-03-31')
 insert into #tmp_b(ID,消费,日期) values(2,12,'2016-02-25')

drop table #tmp_c
create table #tmp_c(ID int,取现 numeric(12,2),日期 datetime)
 insert into #tmp_c(ID,取现,日期) values(2,30,'2016-03-15')

drop table #tmp_d
create table #tmp_d(ID int,充值 numeric(12,2),日期 datetime,备注 varchar(20))
 insert into #tmp_d(ID,充值,日期,备注) values(1,200,'2016-03-15','sys')
 insert into #tmp_d(ID,充值,日期,备注) values(3,40,'2016-03-13','xls')
 insert into #tmp_d(ID,充值,日期,备注) values(1,100,'2016-03-14','sys')

--查询数据
declare @dt datetime
set @dt='2016-03-01'

select a.姓名, b.消费,c.取现, d.充值sys, d.充值xls
from #tmp_a as a
 left join(
           select ID,
           sum(消费) as 消费
           from #tmp_b
           where year(日期)=year(@dt) and month(日期)=month(@dt)
           group by id
          )b
 on a.id=b.id
 left join(
           select ID,
           sum(取现) as 取现
           from #tmp_c
           where year(日期)=year(@dt) and month(日期)=month(@dt)
           group by id
          )c
on a.id=c.id
 left join(
           select ID,
           sum(case when 备注='sys' then 充值 else 0 end) as [充值sys],
           sum(case when 备注='xls' then 充值 else 0 end) as [充值xls]
           from #tmp_d
           where year(日期)=year(@dt) and month(日期)=month(@dt)
           group by id
          )d
on a.id =d.id
--水平有限，不知道怎么把NULL换成0

[此贴子已经被作者于2016-4-9 13:24编辑过]

把2楼的
select a.姓名, b.消费,c.取现, d.充值sys, d.充值xls
改为
select a.姓名, isnull(b.消费,0)消费,isnull(c.取现,0)取现, isnull(d.充值sys,0)充值sys, isnull(d.充值xls,0)充值xls
就可以去掉 .NULL.

--建立数据
drop table #tmp_a
create table #tmp_a(ID int,姓名 varchar(20))
 insert into #tmp_a(ID,姓名) values(1,'A1')
 insert into #tmp_a(ID,姓名) values(2,'B1')
 insert into #tmp_a(ID,姓名) values(3,'C1')

drop table #tmp_b
create table #tmp_b(ID int,消费 numeric(12,2),日期 datetime)
 insert into #tmp_b(ID,消费,日期) values(1,100,'2016-03-01')
 insert into #tmp_b(ID,消费,日期) values(3,50,'2016-03-02')
 insert into #tmp_b(ID,消费,日期) values(3,20,'2016-03-31')
 insert into #tmp_b(ID,消费,日期) values(2,12,'2016-02-25')

drop table #tmp_c
create table #tmp_c(ID int,取现 numeric(12,2),日期 datetime)
 insert into #tmp_c(ID,取现,日期) values(2,30,'2016-03-15')

drop table #tmp_d
create table #tmp_d(ID int,充值 numeric(12,2),日期 datetime,备注 varchar(20))
 insert into #tmp_d(ID,充值,日期,备注) values(1,200,'2016-03-15','sys')
 insert into #tmp_d(ID,充值,日期,备注) values(3,40,'2016-03-13','xls')
 insert into #tmp_d(ID,充值,日期,备注) values(1,100,'2016-03-14','sys')

--查询数据
declare @dt datetime
set @dt='2016-03-01'

select a.姓名, isnull(b.消费,0)消费,isnull(c.取现,0)取现, isnull(d.充值sys,0)充值sys, isnull(d.充值xls,0)充值xls
from #tmp_a as a
 left join(
           select ID,
           sum(消费) as 消费
           from #tmp_b
           where year(日期)=year(@dt) and month(日期)=month(@dt)
           group by id
          )b
 on a.id=b.id
 left join(
           select ID,
           sum(取现) as 取现
           from #tmp_c
           where year(日期)=year(@dt) and month(日期)=month(@dt)
           group by id
          )c
on a.id=c.id
 left join(
           select ID,
           sum(case when 备注='sys' then 充值 else 0 end) as [充值sys],
           sum(case when 备注='xls' then 充值 else 0 end) as [充值xls]
           from #tmp_d
           where year(日期)=year(@dt) and month(日期)=month(@dt)
           group by id
          )d
on a.id =d.id