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 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=? 就可以了,就是把单位也过滤掉,但是我不知道加哪。
我在上面写了可是不对,请帮忙再给写一下好吗?谢谢!
错误来源: ORA-01008: 并非所有变量都已关联
(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=?
)
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 a.tab_year || lpad(a.tab_month, 2, '0') = to_char(add_months(sysdate,-2),'yyyymm') and a.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 b.dw=?
)
试试这个,看行不行
(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=?
)
(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,dw,
--要求显示上个月的字段
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,
lag(dw, 1, 0) over(partition by xm order by tab_year||lpad(tab_month, 2, '0')) as dw1
--要求显示上上个月的字段
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, 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'), dw
--要求显示上上个月的字段
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 b.dw=?
)
我用的是oracle数据库。