笨猫(*)，笨猫(*)，笨猫(*)，笨猫(*)进，关于上次的sql语句！急..在线等!
select * from
(select dy_dcsw,lj_dcsw,dy_zr,lj_zr,dy_zr_qz,lj_zr_qz,dy_zc,
lj_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,xm,
--要求显示上个月的字段
lag(lj_dcsw , 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0 ')) as lj_dcsw1 , --加上partition by
lag(lj_zr , 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0 ')) as lj_zr1 ,
lag(lj_zr_qz , 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0 ')) as lj_zr_qz1 ,
lag(lj_zc , 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0 ')) as lj_zc1 ,
lag(lj_sdl , 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0 ')) as lj_sdl1 ,
lag(lj_sdl_qz, 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0 ')) as lj_sdl_qz1,
lag(lj_xsdl , 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0 ')) as lj_xsdl1 ,
lag(lj_xsl , 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0 ')) as lj_xsl1 ,
lag(lj_xsl_ys, 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0 ')) as lj_xsl_ys1,
lag(tab_year , 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0 ')) as tab_year1 ,
lag(lpad(tab_month, 2, '0 '), 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0 ')) as tab_month1
--要求显示上上个月的字段
from 分压线损表
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 ') and dw=?
)
where tab_year || lpad(tab_month, 2, '0 ') = to_char(add_months(sysdate,-1), 'yyyymm ') and dw=?
union all
--处理上月不存在纪录而上上月存在纪录的情况
select null, null,null,null,null,null,null,
null,null,null,null,null,null,null,
null,null,null,null,null, null,xm,
--要求显示上个月的字段
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 分压线损表 a
where tab_year || lpad(tab_month, 2, '0 ') = to_char(add_months(sysdate,-2), 'yyyymm ') and dw=?
and not exists
(select 1 from 分压线损表 b where a.xm = b.xm
and b.tab_year || lpad(b.tab_month, 2, '0 ') = to_char(add_months(sysdate,-1), 'yyyymm ') and dw=?
)
这是上次你给写的sql语句，我现在要加一个限制条件参数，就是dw(单位),它是从外面传递过来的。
直接加: and dw=? 就可以了，就是把单位也过滤掉，但是我不知道加哪。
我在上面写了可是不对，请帮忙再给写一下好吗？
谢谢！
[解决办法]
select * from
(select dy_dcsw,lj_dcsw,dy_zr,lj_zr,dy_zr_qz,lj_zr_qz,dy_zc,
lj_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,xm,
--要求显示上个月的字段
lag(lj_dcsw , 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0 ')) as lj_dcsw1 , --加上partition by
lag(lj_zr , 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0 ')) as lj_zr1 ,
lag(lj_zr_qz , 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0 ')) as lj_zr_qz1 ,
lag(lj_zc , 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0 ')) as lj_zc1 ,
lag(lj_sdl , 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0 ')) as lj_sdl1 ,
lag(lj_sdl_qz, 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0 ')) as lj_sdl_qz1,
lag(lj_xsdl , 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0 ')) as lj_xsdl1 ,
lag(lj_xsl , 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0 ')) as lj_xsl1 ,
lag(lj_xsl_ys, 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0 ')) as lj_xsl_ys1,
lag(tab_year , 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0 ')) as tab_year1 ,
lag(lpad(tab_month, 2, '0 '), 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0 ')) as tab_month1
--要求显示上上个月的字段
from 分压线损表
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 ') and dw=?
)
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,xm,
--要求显示上个月的字段
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 分压线损表 a
where tab_year || lpad(tab_month, 2, '0 ') = to_char(add_months(sysdate,-2), 'yyyymm ') and dw=?
and not exists
(select 1 from 分压线损表 b where a.xm = b.xm
and b.tab_year || lpad(b.tab_month, 2, '0 ') = to_char(add_months(sysdate,-1), 'yyyymm ') and dw=?
)
