请教一个SQL问题，谢谢！~

[xieye]

三个表：tblArea，tblProvince，tblData
tblArea(地区表，如华北地区，东北地区等)：
AreaID 地区代码
Areaname 地区名称
AreaLevel 地区级别(为0的是需要的)

tblProvince(省市自治区表，包含全国所有的省市自治区)：
ProvID 省市代码
Province 省市名称
AreaID 所属地区代码

tblData：
DataDate 日期(精确到月份，是6位char类型的，如200501)
Amount 数量
ProvID 省市代码


先从tblArea中选出AreaLevel为0的(国内地区)，再每个地区所有的省市的每个月的Amount总和；要求不管该地区该月有没有数据都显示(没有数据则为0)，出来的结果应该是84条数据(一共有7个地区，每个地区有12个月的数据)。

我用了下面的sql语句，可总是不显示没数据的：
select d.DataDate DataDate,a.AreaID AID,sum(d.Amount) sumAmount
from tblData d right outer join (tblProvince p right outer join tblArea a on (p.AreaID=a.AreaID)) on d.ProvID=p.ProvID
where d.DataDate like '2005%' and a.AreaLevel='0'
group by a.AreaID,DataDate

请问应该如何解决这个问题？谢谢！~

[子陌红尘]

如果存在缺月的情况：
---------------------------------------------
select
b.DataDate,
a.AreaID,
sumAmount = isnull(sum(d.Amount))
from
tblArea a
inner join
(select '200501' as DataDate union
select '200502' union
select '200503' union
select '200504' union
select '200505' union
select '200506' union
select '200507' union
select '200508' union
select '200509' union
select '200510' union
select '200511' union
select '200512') b
on
1 = 1
left join
tblProvince c
on
a.AreaID = c.AreaID
left join
tblData d
on
c.ProvID = d.ProvID and b.DataDate = d.DataDate
where
a.AreaLevel = '0'
group by
a.AreaID,b.DataDate

[xieye]

谢谢libin_ftsafe(子陌红尘)！~测试结果完全正确:)

[子陌红尘]

try:
----------------------------------------------------------------------
select
b.DataDate,
a.AreaID,
sumAmount = isnull(sum(d.Amount))
from
tblArea a
inner join
(select distinct DataDate from tblData where DataDate like '2005%')
on
1 = 1
left join
tblProvince c
on
a.AreaID = c.AreaID
left join
tblData d
on
c.ProvID = d.ProvID and b.DataDate = d.DataDate
where
a.AreaLevel = '0'
group by
a.AreaID,b.DataDate

[xieye]

inner join
(select distinct DataDate from tblData where DataDate like '2005%')
on
1 = 1
-------------------------
请问这段什么意思？

[zjcxc]

--要考虑缺月份的问题

select c.DataDate DataDate,a.AreaID AID,sum(c.Amount) sumAmount
from(
select a.AreaID,b.[month]
from tblArea a,(
select [month]=200501 union all select 200502 union all
select [month]=200503 union all select 200504 union all
select [month]=200505 union all select 200506 union all
select [month]=200507 union all select 200508 union all
select [month]=200509 union all select 200510 union all
select [month]=200511 union all select 200512
)b where a.AreaLevel='0'
)a
left join tblProvince b on a.AreaID=b.AreaID
left join tblData c on b.ProvID=c.ProvID and a.[month]=convert(char(8),c.DataDate,112)
group by c.DataDate,a.AreaID

[xieye]

谢谢zjcxc(邹建)！~
我测试的数据中只有两条data数据(ProvID为01和05都是AreaID为01的)：
DataDate Amount ProvID
200501 1 01
200502 2 05

运行你给的语句的结果：
DataDate AID sumAmount
NULL 01 NULL
200501 01 1.0
200502 01 2.0
NULL 02 NULL
NULL 03 NULL
NULL 04 NULL
NULL 05 NULL
NULL 06 NULL
NULL 07 NULL

我想要的结果是每个AID对应有12条记录，应该有84条记录啊，请问应该怎么写？