;with cte as ( select b1.dptid,b1.dptname ,sum(b2.age1) as age1 ,sum(b2.age2) as age2 ,sum(b2.age3) as age3 ,sum(b2.age4) as age4 ,sum(b2.qty) as qty ,b1.year,b1.month from b b1 left join b b2 on b1.dptid = b2.dptid and b1.dptname=b2.dptname and (b1.year < b2.year or b1.year = b2.year and b1.month<=b2.month) group by b1.dptid,b1.dptname ,b1.year,b1.month ) select a.dptid,a.dptname ,a.age1 - b.age1 as age1 ,a.age2 - b.age2 as age2 ,a.age3 - b.age3 as age3 ,a.age4 - b.age4 as age4 ,a.qty - b.qty as qty ,b.year,b.month from a,cte as b where a.dptid = b.dptid and a.dptname=b.dptname
;WITH t (dptid,dptname,year,month,age1,age2,age3,age4,qty) AS( SELECT top 1 a.dptid, a.dptname, b.year, b.month, a.age1-b.age1 AS age1, a.age2-b.age2 AS age2, a.age3-b.age3 AS age3, a.age4-b.age4 AS age4, a.qty-b.qty AS qty FROM (SELECT dptid,dptname,age1,age2,age3,age4,qty,year,month FROM a WHERE month=1 AND year=2013) AS a left join b on a.dptid=b.dptid order by cast (b.month as int) desc
UNION ALL SELECT b.dptid, b.dptname, b.year, b.month, t.age1-b.age1 AS age1, t.age2-b.age2 AS age2, t.age3-b.age3 AS age3, t.age4-b.age4 AS age4, t.qty-b.qty AS qty FROM b as b,t WHERE b.year=t.year and b.month=t.month-1 ) select * from t 这是我写的,但是因为6月份数据都为0了,5月和2月的就不继续递归了
上面语句有问题的原因是你的month是字符类型,排序出现问题 ;with cte as ( select b1.dptid,b1.dptname ,sum(b2.age1) as age1 ,sum(b2.age2) as age2 ,sum(b2.age3) as age3 ,sum(b2.age4) as age4 ,sum(b2.qty) as qty ,b1.year,b1.month from b b1 left join b b2 on b1.dptid = b2.dptid and b1.dptname=b2.dptname and (b1.year < b2.year or b1.year = b2.year and cast(b1.month as int)<=cast(b2.month as int)) group by b1.dptid,b1.dptname ,b1.year,b1.month ) select a.dptid,a.dptname ,a.age1 - b.age1 as age1 ,a.age2 - b.age2 as age2 ,a.age3 - b.age3 as age3 ,a.age4 - b.age4 as age4 ,a.qty - b.qty as qty ,b.year,b.month from a,cte as b where a.dptid = b.dptid and a.dptname=b.dptname order by a.dptid,a.dptname ,b.year desc ,cast(b.month as int) desc
select b1.dptid,b1.dptname
,sum(b2.age1) as age1
,sum(b2.age2) as age2
,sum(b2.age3) as age3
,sum(b2.age4) as age4
,sum(b2.qty) as qty
,b1.year,b1.month
from b b1 left join b b2
on b1.dptid = b2.dptid and b1.dptname=b2.dptname
and (b1.year < b2.year
or b1.year = b2.year and b1.month<=b2.month)
group by b1.dptid,b1.dptname
,b1.year,b1.month
)
select
a.dptid,a.dptname
,a.age1 - b.age1 as age1
,a.age2 - b.age2 as age2
,a.age3 - b.age3 as age3
,a.age4 - b.age4 as age4
,a.qty - b.qty as qty
,b.year,b.month
from a,cte as b
where a.dptid = b.dptid and a.dptname=b.dptname
SELECT top 1 a.dptid,
a.dptname,
b.year,
b.month,
a.age1-b.age1 AS age1,
a.age2-b.age2 AS age2,
a.age3-b.age3 AS age3,
a.age4-b.age4 AS age4,
a.qty-b.qty AS qty
FROM
(SELECT dptid,dptname,age1,age2,age3,age4,qty,year,month FROM a WHERE month=1 AND year=2013) AS a
left join b on a.dptid=b.dptid
order by cast (b.month as int) desc
UNION ALL
SELECT b.dptid,
b.dptname,
b.year,
b.month,
t.age1-b.age1 AS age1,
t.age2-b.age2 AS age2,
t.age3-b.age3 AS age3,
t.age4-b.age4 AS age4,
t.qty-b.qty AS qty
FROM b as b,t
WHERE b.year=t.year
and b.month=t.month-1
)
select * from t
这是我写的,但是因为6月份数据都为0了,5月和2月的就不继续递归了
;with cte as (
select b1.dptid,b1.dptname
,sum(b2.age1) as age1
,sum(b2.age2) as age2
,sum(b2.age3) as age3
,sum(b2.age4) as age4
,sum(b2.qty) as qty
,b1.year,b1.month
from b b1 left join b b2
on b1.dptid = b2.dptid and b1.dptname=b2.dptname
and (b1.year < b2.year
or b1.year = b2.year and cast(b1.month as int)<=cast(b2.month as int))
group by b1.dptid,b1.dptname
,b1.year,b1.month
)
select
a.dptid,a.dptname
,a.age1 - b.age1 as age1
,a.age2 - b.age2 as age2
,a.age3 - b.age3 as age3
,a.age4 - b.age4 as age4
,a.qty - b.qty as qty
,b.year,b.month
from a,cte as b
where a.dptid = b.dptid and a.dptname=b.dptname
order by a.dptid,a.dptname
,b.year desc
,cast(b.month as int) desc