在PL/SQL里显示不出来。确定有数据的,我用 select * from 分压线损表 where tab_year='2007' and tab_month='1'可以显示出我的三条记录的。
理解了,tab_month的字段存储的月份不是按照'MM'格式来存储的
select * from (select 系列字段, dy_dcsw,lj_dcsw,dy_zr,lj_zr,dy_zr_qz,lj_zr_qz,dy_zc, j_zc,dy_sdl,lj_sdl,dy_sdl_qz,lj_sdl_qz,dy_xsdl,lj_xsdl, dy_xsl,lj_xsl,dy_xsl_ys,lj_xsl_ys,tab_year, lpad(tab_month, 2, '0') tab_month, --要求显示上个月的字段 lag(lj_dcsw , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_dcsw1 , --加上partition by lag(lj_zr , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_zr1 , lag(lj_zr_qz , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_zr_qz1 , lag(lj_zc , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_zc1 , lag(lj_sdl , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_sdl1 , lag(lj_sdl_qz, 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_sdl_qz1, lag(lj_xsdl , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_xsdl1 , lag(lj_xsl , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_xsl1 , lag(lj_xsl_ys, 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_xsl_ys1 --要求显示上上个月的字段 from table_name where tab_year || lpad(tab_month, 2, '0') >= to_char(add_months(sysdate,-2),'yyyymm') and tab_year || lpad(tab_month, 2, '0') <= to_char(add_months(sysdate,-1),'yyyymm') ) where tab_year || lpad(tab_month, 2, '0') = to_char(add_months(sysdate,-1),'yyyymm') union all --处理上月不存在纪录而上上月存在纪录的情况 select 系列字段, null, null,null,null,null,null,null, null,null,null,null,null,null,null, null,null,null,null,null, null, --要求显示上个月的字段 lj_dcsw as lj_dcsw1, lj_zr as lj_zr1 ,lj_zr_qz as lj_zr_qz1 ,lj_zc as lj_zc1 ,lj_sdl as lj_sdl1 , lj_sdl_qz as lj_sdl_qz1, lj_xsdl as lj_xsdl1 , lj_xsl as lj_xsl1 ,lj_xsl_ys as lj_xsl_ys1 --要求显示上上个月的字段 from table_name a where tab_year || lpad(tab_month, 2, '0') = to_char(add_months(sysdate,-2),'yyyymm') and not exists (select 1 from table_name b where a.系列字段 = b.系列字段 and b.tab_year || lpad(b.tab_month, 2, '0') = to_char(add_months(sysdate,-1),'yyyymm')
select * from (select 系列字段, dy_dcsw,lj_dcsw,dy_zr,lj_zr,dy_zr_qz,lj_zr_qz,dy_zc, j_zc,dy_sdl,lj_sdl,dy_sdl_qz,lj_sdl_qz,dy_xsdl,lj_xsdl, dy_xsl,lj_xsl,dy_xsl_ys,lj_xsl_ys,tab_year, lpad(tab_month, 2, '0') tab_month, --要求显示上个月的字段 lag(lj_dcsw , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_dcsw1 , --加上partition by lag(lj_zr , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_zr1 , lag(lj_zr_qz , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_zr_qz1 , lag(lj_zc , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_zc1 , lag(lj_sdl , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_sdl1 , lag(lj_sdl_qz, 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_sdl_qz1, lag(lj_xsdl , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_xsdl1 , lag(lj_xsl , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_xsl1 , lag(lj_xsl_ys, 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_xsl_ys1, lag(tab_year , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as tab_year1 , lag(lpad(tab_month, 2, '0'), 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as tab_month1, --要求显示上上个月的字段 from table_name where tab_year || lpad(tab_month, 2, '0') >= to_char(add_months(sysdate,-2),'yyyymm') and tab_year || lpad(tab_month, 2, '0') <= to_char(add_months(sysdate,-1),'yyyymm') ) where tab_year || lpad(tab_month, 2, '0') = to_char(add_months(sysdate,-1),'yyyymm') union all --处理上月不存在纪录而上上月存在纪录的情况 select 系列字段, null, null,null,null,null,null,null, null,null,null,null,null,null,null, null,null,null,null,null, null, --要求显示上个月的字段 lj_dcsw as lj_dcsw1, lj_zr as lj_zr1 ,lj_zr_qz as lj_zr_qz1 ,lj_zc as lj_zc1 ,lj_sdl as lj_sdl1 , lj_sdl_qz as lj_sdl_qz1, lj_xsdl as lj_xsdl1 , lj_xsl as lj_xsl1 ,lj_xsl_ys as lj_xsl_ys1, tab_year, lpad(tab_month, 2, '0') --要求显示上上个月的字段 from table_name a where tab_year || lpad(tab_month, 2, '0') = to_char(add_months(sysdate,-2),'yyyymm') and not exists (select 1 from table_name b where a.系列字段 = b.系列字段 and b.tab_year || lpad(b.tab_month, 2, '0') = to_char(add_months(sysdate,-1),'yyyymm')
请将问题仔细描述清楚,别人才好帮你找问题原因.
(select 系列字段, dy_dcsw,lj_dcsw,dy_zr,lj_zr,dy_zr_qz,lj_zr_qz,dy_zc,
j_zc,dy_sdl,lj_sdl,dy_sdl_qz,lj_sdl_qz,dy_xsdl,lj_xsdl,
dy_xsl,lj_xsl,dy_xsl_ys,lj_xsl_ys,tab_year, lpad(tab_month, 2, '0') tab_month,
--要求显示上个月的字段
lag(lj_dcsw , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_dcsw1 , --加上partition by
lag(lj_zr , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_zr1 ,
lag(lj_zr_qz , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_zr_qz1 ,
lag(lj_zc , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_zc1 ,
lag(lj_sdl , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_sdl1 ,
lag(lj_sdl_qz, 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_sdl_qz1,
lag(lj_xsdl , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_xsdl1 ,
lag(lj_xsl , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_xsl1 ,
lag(lj_xsl_ys, 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_xsl_ys1
--要求显示上上个月的字段
from table_name
where tab_year || lpad(tab_month, 2, '0') >= to_char(add_months(sysdate,-2),'yyyymm')
and tab_year || lpad(tab_month, 2, '0') <= to_char(add_months(sysdate,-1),'yyyymm')
)
where tab_year || lpad(tab_month, 2, '0') = to_char(add_months(sysdate,-1),'yyyymm')
union all
--处理上月不存在纪录而上上月存在纪录的情况
select 系列字段, null, null,null,null,null,null,null,
null,null,null,null,null,null,null,
null,null,null,null,null, null,
--要求显示上个月的字段
lj_dcsw as lj_dcsw1, lj_zr as lj_zr1 ,lj_zr_qz as lj_zr_qz1 ,lj_zc as lj_zc1 ,lj_sdl as lj_sdl1 ,
lj_sdl_qz as lj_sdl_qz1, lj_xsdl as lj_xsdl1 , lj_xsl as lj_xsl1 ,lj_xsl_ys as lj_xsl_ys1
--要求显示上上个月的字段
from table_name a
where tab_year || lpad(tab_month, 2, '0') = to_char(add_months(sysdate,-2),'yyyymm')
and not exists
(select 1 from table_name b where a.系列字段 = b.系列字段
and b.tab_year || lpad(b.tab_month, 2, '0') = to_char(add_months(sysdate,-1),'yyyymm')
(select 系列字段, dy_dcsw,lj_dcsw,dy_zr,lj_zr,dy_zr_qz,lj_zr_qz,dy_zc,
j_zc,dy_sdl,lj_sdl,dy_sdl_qz,lj_sdl_qz,dy_xsdl,lj_xsdl,
dy_xsl,lj_xsl,dy_xsl_ys,lj_xsl_ys,tab_year, lpad(tab_month, 2, '0') tab_month,
--要求显示上个月的字段
lag(lj_dcsw , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_dcsw1 , --加上partition by
lag(lj_zr , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_zr1 ,
lag(lj_zr_qz , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_zr_qz1 ,
lag(lj_zc , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_zc1 ,
lag(lj_sdl , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_sdl1 ,
lag(lj_sdl_qz, 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_sdl_qz1,
lag(lj_xsdl , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_xsdl1 ,
lag(lj_xsl , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_xsl1 ,
lag(lj_xsl_ys, 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as lj_xsl_ys1,
lag(tab_year , 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as tab_year1 ,
lag(lpad(tab_month, 2, '0'), 1, 0) over(partition by 系列字段 order by tab_year||lpad(tab_month, 2, '0')) as tab_month1,
--要求显示上上个月的字段
from table_name
where tab_year || lpad(tab_month, 2, '0') >= to_char(add_months(sysdate,-2),'yyyymm')
and tab_year || lpad(tab_month, 2, '0') <= to_char(add_months(sysdate,-1),'yyyymm')
)
where tab_year || lpad(tab_month, 2, '0') = to_char(add_months(sysdate,-1),'yyyymm')
union all
--处理上月不存在纪录而上上月存在纪录的情况
select 系列字段, null, null,null,null,null,null,null,
null,null,null,null,null,null,null,
null,null,null,null,null, null,
--要求显示上个月的字段
lj_dcsw as lj_dcsw1, lj_zr as lj_zr1 ,lj_zr_qz as lj_zr_qz1 ,lj_zc as lj_zc1 ,lj_sdl as lj_sdl1 ,
lj_sdl_qz as lj_sdl_qz1, lj_xsdl as lj_xsdl1 , lj_xsl as lj_xsl1 ,lj_xsl_ys as lj_xsl_ys1, tab_year, lpad(tab_month, 2, '0')
--要求显示上上个月的字段
from table_name a
where tab_year || lpad(tab_month, 2, '0') = to_char(add_months(sysdate,-2),'yyyymm')
and not exists
(select 1 from table_name b where a.系列字段 = b.系列字段
and b.tab_year || lpad(b.tab_month, 2, '0') = to_char(add_months(sysdate,-1),'yyyymm')
1 1 1 2007 1 22 33
2 2 2 2007 1 22 33
22 33
22 33我想让它把2006 12也对应的显示出来,可以吗?如果不可以就算了。