求助：我想查询sql里面12年10月至13年4月这段时间的使用热量，如果这个月没有记录，时间字段显示时间，使用热量显示0，应该怎么修改。select top 7 SUBSTRING(a.dtime1,1,7) as dtime1, case when a.sum1-b.SUM2 <0 then 0 else a.sum1-b.SUM2 end from
(select top 12 SUBSTRING(时间,1,7) as dtime1 ,SUM(累计热量) as sum1 from VMeterInfo where (SUBSTRING(时间,1,7) between substring('2012-11-01',1,7)and substring('2013-04-30',1,7)) and 小区编号=288 group by SUBSTRING(时间,1,7) order by SUBSTRING(时间,1,7))a,
(select top 12 SUBSTRING(时间,1,7) as dtime2,SUM(累计热量) as sum2 from VMeterInfo where (SUBSTRING(时间,1,7) between substring('2012-10-01',1,7)and substring('2013-03-30',1,7)) and 小区编号=288 group by SUBSTRING(时间,1,7)order by SUBSTRING(时间,1,7))b
where SUBSTRING(a.dtime1,6,7)=SUBSTRING(b.dtime2,6,7)+1 or SUBSTRING(a.dtime1,6,7)=SUBSTRING(b.dtime2,6,7)-11
这是没有记录的时候显示的，但是我想没有记录的时候也会显示6条记录，但是使用热量那栏都是0
------解决方案--------------------
--try this
select top 7 SUBSTRING(a.dtime1,1,7) as dtime1, case when a.sum1-b.SUM2 <0 then 0 else a.sum1-b.SUM2 end from
(select top 12 SUBSTRING(时间,1,7) as dtime1 ,SUM(累计热量) as sum1 from VMeterInfo where (SUBSTRING(时间,1,7) between substring('2012-11-01',1,7)and substring('2013-04-30',1,7)) and 小区编号=288 group by SUBSTRING(时间,1,7) order by SUBSTRING(时间,1,7))a LEFT JOIN
(select top 12 SUBSTRING(时间,1,7) as dtime2,SUM(累计热量) as sum2 from VMeterInfo where (SUBSTRING(时间,1,7) between substring('2012-10-01',1,7)and substring('2013-03-30',1,7)) and 小区编号=288 group by SUBSTRING(时间,1,7)order by SUBSTRING(时间,1,7))b
on SUBSTRING(a.dtime1,6,7)=SUBSTRING(b.dtime2,6,7)+1 or SUBSTRING(a.dtime1,6,7)=SUBSTRING(b.dtime2,6,7)-11
------解决方案--------------------
最好是先构造出 从12年4月-13年3月的日期数据，然后用这个数据左关联你的业务数据
------解决方案--------------------
仔细看了一下，你这时要求 环比是吧。
------解决方案--------------------
try this,
declare @x1 varchar(15),@x2 varchar(15)
select @x1='2012-10-01',@x2='2013-04-30' --> 定义开始时间,结束时间
select c.dtime,isnull(d.qty,0) '使用热量'
from
(select left(convert(varchar,dateadd(m,number,@x1),23),7) 'dtime'
from master.dbo.spt_values
where type='P' and number between 0 and datediff(m,@x1,@x2)) c
left join
(select top 7 SUBSTRING(a.dtime1,1,7) as dtime1,
case when a.sum1-b.SUM2 <0 then 0 else a.sum1-b.SUM2 end 'qty'
from
(select top 12 SUBSTRING(时间,1,7) as dtime1,
SUM(累计热量) as sum1
from VMeterInfo
where (SUBSTRING(时间,1,7)
between substring('2012-11-01',1,7) and substring('2013-04-30',1,7))
and 小区编号=288
group by SUBSTRING(时间,1,7)
order by SUBSTRING(时间,1,7)) a,
(select top 12 SUBSTRING(时间,1,7) as dtime2,
SUM(累计热量) as sum2
from VMeterInfo
where (SUBSTRING(时间,1,7)
between substring('2012-10-01',1,7) and substring('2013-03-30',1,7))
and 小区编号=288
group by SUBSTRING(时间,1,7)
order by SUBSTRING(时间,1,7)) b
where SUBSTRING(a.dtime1,6,7)=SUBSTRING(b.dtime2,6,7)+1
or SUBSTRING(a.dtime1,6,7)=SUBSTRING(b.dtime2,6,7)-11) d on c.dtime=d.dtime1