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
----------------------------------------------------------------------
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
(select distinct DataDate from tblData where DataDate like '2005%')
on
1 = 1
-------------------------
请问这段什么意思?
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
我测试的数据中只有两条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条记录啊,请问应该怎么写?
---------------------------------------------
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