select a.ZWFZJE_KJND ,a.ZWFZJE_KJQJ,a.ZWFZJE_KMBH,a.ZWFZJE_BMBH,sum(a.ZWFZJE_JFLJ) as BMBYLJ,(select sum(b.ZWFZJE_JFLJ) from lc0209999.ZWFZJE b where (b.ZWFZJE_KJQJ<=a.ZWFZJE_KJQJ)
and a.ZWFZJE_KJND=b.ZWFZJE_KJND
and a.ZWFZJE_KMBH=b.ZWFZJE_KMBH
and a.ZWFZJE_BMBH=b.ZWFZJE_BMBH ) as BMBNLJfrom lc0209999.ZWFZJE a
where (a.ZwFZJE_KMBH like '6601%' or a.ZwFZJE_KMBH like '6602%' or a.ZwFZJE_KMBH like '5102%')
--and a.ZWFZJE_KJND='2012'and a.ZWFZJE_KMBH='6601001' and a.ZWFZJE_BMBH = '1501'
group by a.ZWFZJE_KJND,a.ZWFZJE_KJQJ,a.ZWFZJE_KMBH,a.ZWFZJE_BMBH
order by a.ZWFZJE_KJND,a.ZWFZJE_KJQJ,a.ZWFZJE_KMBH,a.ZWFZJE_BMBH
2012 01 6601001 01 0.000000000000 0.000000000000 NULL
2012 01 6601001 14 0.000000000000 0.000000000000 NULL
2012 01 6601001 1501 0.000000000000 0.000000000000 NULL
2012 01 6601001 1502 0.000000000000 0.000000000000 NULL
2012 01 6601001 1503 0.000000000000 0.000000000000 NULL
2012 01 6601001 1504 0.000000000000 0.000000000000 NULL
2012 01 6601001 1506 0.000000000000 0.000000000000 NULL
2012 01 6601001 1508 0.000000000000 0.000000000000 NULL
2012 01 6601001 1510 0.000000000000 0.000000000000 NULL
2012 01 6601001 1511 0.000000000000 0.000000000000 NULL
2012 01 6601001 1514 0.000000000000 0.000000000000 NULL
2012 01 6601001 1516 0.000000000000 0.000000000000 NULL
2012 01 6601001 1517 0.000000000000 0.000000000000 NULL
2012 01 6601002 1501 0.000000000000 0.000000000000 NULL
2012 01 6601002 1502 0.000000000000 0.000000000000 NULL
2012 01 6601002 1508 0.000000000000 0.000000000000 NULL
2012 01 6601002 1511 0.000000000000 0.000000000000 NULL在这个结果后面在添加一栏 数据是第一栏减1,在结果中的数据。就是2012-1=2011 结果中2011的数据
and a.ZWFZJE_KJND=b.ZWFZJE_KJND
and a.ZWFZJE_KMBH=b.ZWFZJE_KMBH
and a.ZWFZJE_BMBH=b.ZWFZJE_BMBH ) as BMBNLJfrom lc0209999.ZWFZJE a
where (a.ZwFZJE_KMBH like '6601%' or a.ZwFZJE_KMBH like '6602%' or a.ZwFZJE_KMBH like '5102%')
--and a.ZWFZJE_KJND='2012'and a.ZWFZJE_KMBH='6601001' and a.ZWFZJE_BMBH = '1501'
group by a.ZWFZJE_KJND,a.ZWFZJE_KJQJ,a.ZWFZJE_KMBH,a.ZWFZJE_BMBH
order by a.ZWFZJE_KJND,a.ZWFZJE_KJQJ,a.ZWFZJE_KMBH,a.ZWFZJE_BMBH
2012 01 6601001 01 0.000000000000 0.000000000000 NULL
2012 01 6601001 14 0.000000000000 0.000000000000 NULL
2012 01 6601001 1501 0.000000000000 0.000000000000 NULL
2012 01 6601001 1502 0.000000000000 0.000000000000 NULL
2012 01 6601001 1503 0.000000000000 0.000000000000 NULL
2012 01 6601001 1504 0.000000000000 0.000000000000 NULL
2012 01 6601001 1506 0.000000000000 0.000000000000 NULL
2012 01 6601001 1508 0.000000000000 0.000000000000 NULL
2012 01 6601001 1510 0.000000000000 0.000000000000 NULL
2012 01 6601001 1511 0.000000000000 0.000000000000 NULL
2012 01 6601001 1514 0.000000000000 0.000000000000 NULL
2012 01 6601001 1516 0.000000000000 0.000000000000 NULL
2012 01 6601001 1517 0.000000000000 0.000000000000 NULL
2012 01 6601002 1501 0.000000000000 0.000000000000 NULL
2012 01 6601002 1502 0.000000000000 0.000000000000 NULL
2012 01 6601002 1508 0.000000000000 0.000000000000 NULL
2012 01 6601002 1511 0.000000000000 0.000000000000 NULL在这个结果后面在添加一栏 数据是第一栏减1,在结果中的数据。就是2012-1=2011 结果中2011的数据
(select sum(b.ZWFZJE_JFLJ) from lc0209999.ZWFZJE b where (b.ZWFZJE_KJQJ<=a.ZWFZJE_KJQJ)
and a.ZWFZJE_KJND=b.ZWFZJE_KJND
and a.ZWFZJE_KMBH=b.ZWFZJE_KMBH
and a.ZWFZJE_BMBH=b.ZWFZJE_BMBH) as BMBNLJ,
a.ZWFZJE_KJND-1 'new_col'
from lc0209999.ZWFZJE a
where (a.ZwFZJE_KMBH like '6601%' or a.ZwFZJE_KMBH like '6602%' or a.ZwFZJE_KMBH like '5102%')
--and a.ZWFZJE_KJND='2012'and a.ZWFZJE_KMBH='6601001' and a.ZWFZJE_BMBH = '1501'
group by a.ZWFZJE_KJND,a.ZWFZJE_KJQJ,a.ZWFZJE_KMBH,a.ZWFZJE_BMBH
order by a.ZWFZJE_KJND,a.ZWFZJE_KJQJ,a.ZWFZJE_KMBH,a.ZWFZJE_BMBH
select 2011, 1, '00', '123' union all
select 2010, 1, '00', '321'
select x.a,x.b,x.c,x.d,y.a,y.d
from za x
left join za y on x.a=y.a+1
where y.a is not null/*
a b c d a d
----------- ----------- ---------- ---------- ----------- ----------
2011 1 00 123 2010 321(1 row(s) affected)
*/