这是另一贴:http://community.csdn.net/expert/Topicview2.asp?id=5313692这是所有字段:
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上面的要都显示(上个月的),再加上下面的。这是要显示上上个月的字段:
lj_dcsw,lj_zr,lj_zr_qz,lj_zc,lj_sdl,lj_sdl_qz,lj_xsdl,lj_xsl,lj_xsl_ys表名:分压线损表 可以再给开,加分啊。万分感谢
----------------------------------------------------------------------
<<例如:>>
我数据库中有10个字段吧,表如下:filed1,filed2,filed,3,filed4,filed5,filed6,filed7,filed8,year,month这样的。我想显示成这样的:
select filed1,filed2,filed3,filed4,file,5,filed6,file7,filed8,filed22,filed44,filed66 where...其中select filed1,filed2,filed3,filed4,file,5,filed6,file7,filed8这几个字段是显示上个月的。filed22,filed44,filed66 这几个字段是显示上上个月的(这三个字段是从新起的名字)类似这样的:select num1,num2,year,month,sum,
(select sum from table_name
where year = to_char(add_months(sysdate,-2),'yyyy') and month = to_char(add_months(sysdate,-2),'mm')
)
from table_name
where year = to_char(add_months(sysdate,-1),'yyyy') and month = to_char(add_months(sysdate,-1),'mm'但这报错了,说返回了多条记录。另效率好像太低。
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上面的要都显示(上个月的),再加上下面的。这是要显示上上个月的字段:
lj_dcsw,lj_zr,lj_zr_qz,lj_zc,lj_sdl,lj_sdl_qz,lj_xsdl,lj_xsl,lj_xsl_ys表名:分压线损表 可以再给开,加分啊。万分感谢
----------------------------------------------------------------------
<<例如:>>
我数据库中有10个字段吧,表如下:filed1,filed2,filed,3,filed4,filed5,filed6,filed7,filed8,year,month这样的。我想显示成这样的:
select filed1,filed2,filed3,filed4,file,5,filed6,file7,filed8,filed22,filed44,filed66 where...其中select filed1,filed2,filed3,filed4,file,5,filed6,file7,filed8这几个字段是显示上个月的。filed22,filed44,filed66 这几个字段是显示上上个月的(这三个字段是从新起的名字)类似这样的:select num1,num2,year,month,sum,
(select sum from table_name
where year = to_char(add_months(sysdate,-2),'yyyy') and month = to_char(add_months(sysdate,-2),'mm')
)
from table_name
where year = to_char(add_months(sysdate,-1),'yyyy') and month = to_char(add_months(sysdate,-1),'mm'但这报错了,说返回了多条记录。另效率好像太低。
解决方案 »
- 创建表空间的重做信息问题。
- oracel
- 如何根据某个字段中的关键词进行分辨后,将某条记录在视图中显示多次?
- 在一本书上看到这样一段话:“要缓存的默认序列号个数是20,经验表明这个数量并不够。如果应用程序每秒从序列中选择10次,那么要将缓存值设置为50000。”
- ORA-04091: 表 HNHF.KCSW 发生了变化, 触发器/函数不能读它
- oracle的sql查询语句问题,请教高手!
- 一个简单的存储过程问题
- 怎样将Oracle 8i升级到9i?
- 我把oracle8i的表导出来了,导到9i时却有如下错误:
- 初学Oracle,请教各位大虾。
- oracle 导入问题,急等!
- 我的pl/sql访问不了远程数据库了
(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,
--要求显示上个月的字段
lag(lj_dcsw , 1, 0) over(order by year||month) as lj_dcsw1 ,
lag(lj_zr , 1, 0) over(order by year||month) as lj_zr1 ,
lag(lj_zr_qz , 1, 0) over(order by year||month) as lj_zr_qz1 ,
lag(lj_zc , 1, 0) over(order by year||month) as lj_zc1 ,
lag(lj_sdl , 1, 0) over(order by year||month) as lj_sdl1 ,
lag(lj_sdl_qz, 1, 0) over(order by year||month) as lj_sdl_qz1,
lag(lj_xsdl , 1, 0) over(order by year||month) as lj_xsdl1 ,
lag(lj_xsl , 1, 0) over(order by year||month) as lj_xsl1 ,
lag(lj_xsl_ys, 1, 0) over(order by year||month) as lj_xsl_ys1
--要求显示上上个月的字段
from table_name
where year || month >= to_char(add_months(sysdate,-2),'yyyymm')
and year || month <= to_char(add_months(sysdate,-1),'yyyymm')
)
where year || month = to_char(add_months(sysdate,-1),'yyyymm')
where year = to_char(add_months(sysdate,-1),'yyyy') and month = to_char(add_months(sysdate,-1),'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,
--要求显示上个月的字段
lag(lj_dcsw , 1, 0) over(order by year||month) as lj_dcsw1 ,
lag(lj_zr , 1, 0) over(order by year||month) as lj_zr1 ,
lag(lj_zr_qz , 1, 0) over(order by year||month) as lj_zr_qz1 ,
lag(lj_zc , 1, 0) over(order by year||month) as lj_zc1 ,
lag(lj_sdl , 1, 0) over(order by year||month) as lj_sdl1 ,
lag(lj_sdl_qz, 1, 0) over(order by year||month) as lj_sdl_qz1,
lag(lj_xsdl , 1, 0) over(order by year||month) as lj_xsdl1 ,
lag(lj_xsl , 1, 0) over(order by year||month) as lj_xsl1 ,
lag(lj_xsl_ys, 1, 0) over(order by year||month) as lj_xsl_ys1
--要求显示上上个月的字段
from table_name
where year || month >= to_char(add_months(sysdate,-2),'yyyymm')
and year || month <= to_char(add_months(sysdate,-1),'yyyymm')
)
where year || month = 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, tab_month,
--要求显示上个月的字段
lag(lj_dcsw , 1, 0) over(order by tab_year||tab_month) as lj_dcsw1 ,
lag(lj_zr , 1, 0) over(order by tab_year||tab_month) as lj_zr1 ,
lag(lj_zr_qz , 1, 0) over(order by tab_year||tab_month) as lj_zr_qz1 ,
lag(lj_zc , 1, 0) over(order by tab_year||tab_month) as lj_zc1 ,
lag(lj_sdl , 1, 0) over(order by tab_year||tab_month) as lj_sdl1 ,
lag(lj_sdl_qz, 1, 0) over(order by tab_year||tab_month) as lj_sdl_qz1,
lag(lj_xsdl , 1, 0) over(order by tab_year||tab_month) as lj_xsdl1 ,
lag(lj_xsl , 1, 0) over(order by tab_year||tab_month) as lj_xsl1 ,
lag(lj_xsl_ys, 1, 0) over(order by tab_year||tab_month) as lj_xsl_ys1
--要求显示上上个月的字段
from table_name
where tab_year || tab_month >= to_char(add_months(sysdate,-2),'yyyymm')
and tab_year || tab_month <= to_char(add_months(sysdate,-1),'yyyymm')
)
where tab_year || tab_month = to_char(add_months(sysdate,-1),'yyyymm')
累计1 1 2 3 4 2006 12
累计2 11 22 33 44 2006 12
累计1 55 55 55 55 2006 11
累计2 66 66 66 66 2006 11读出来之后变成了:
系列 filed1 filed2 filed3 filed4 year month filed33 filed44
累计1 1 2 3 4 2006 12 55 55
累计2 11 22 33 44 2006 12 33 44<系列也是数据库中一字段>
2 select serialno, filed1, filed2, filed3, filed4, year, month,
3 lag(serialno, 1, 0) over(partition by serialno order by year||month) as serialno1,
4 lag(filed1, 1, 0) over(order by year||month) as filed11,
5 lag(filed2, 1, 0) over(order by year||month) as filed21,
6 lag(filed3, 1, 0) over(order by year||month) as filed31,
7 lag(filed4, 1, 0) over(order by year||month) as filed41,
8 lag(year, 1, 0) over(order by year||month) as year1,
9 lag(month, 1, 0) over(order by year||month) as month1
10 from test1
11 where year || month >= to_char(add_months(sysdate,-2),'yyyymm')
12 and year || month <= to_char(add_months(sysdate,-1),'yyyymm')
13 )
14 where year || month = to_char(add_months(sysdate,-1),'yyyymm')
15 ;SERIAL FILE FILE FILE FILE YEAR MONT SERIALNO1 FILE FILE FILE FILE YEAR MONT
------ ---- ---- ---- ---- ---- ---- ---------- ---- ---- ---- ---- ---- ----
累计2 11 22 33 44 2006 12 累计2 55 55 55 55 2006 11
累计1 1 2 3 4 2006 12 累计1 11 22 33 44 2006 12SQL> select * from test1;SERIAL FILE FILE FILE FILE YEAR MONT
------ ---- ---- ---- ---- ---- ----
累计1 1 2 3 4 2006 12
累计2 11 22 33 44 2006 12
累计1 55 55 55 55 2006 11
累计2 66 66 66 66 2006 11
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, tab_month,
--要求显示上个月的字段
lag(lj_dcsw , 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_dcsw1 , --加上partition by
lag(lj_zr , 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_zr1 ,
lag(lj_zr_qz , 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_zr_qz1 ,
lag(lj_zc , 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_zc1 ,
lag(lj_sdl , 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_sdl1 ,
lag(lj_sdl_qz, 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_sdl_qz1,
lag(lj_xsdl , 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_xsdl1 ,
lag(lj_xsl , 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_xsl1 ,
lag(lj_xsl_ys, 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_xsl_ys1
--要求显示上上个月的字段
from table_name
where tab_year || tab_month >= to_char(add_months(sysdate,-2),'yyyymm')
and tab_year || tab_month <= to_char(add_months(sysdate,-1),'yyyymm')
)
where tab_year || tab_month = to_char(add_months(sysdate,-1),'yyyymm')
2 select serialno, filed1, filed2, filed3, filed4, year, month,
3 lag(serialno, 1, 0) over(partition by serialno order by year||month) as serialno1,
4 lag(filed1, 1, 0) over(partition by serialno order by year||month) as filed11,
5 lag(filed2, 1, 0) over(partition by serialno order by year||month) as filed21,
6 lag(filed3, 1, 0) over(partition by serialno order by year||month) as filed31,
7 lag(filed4, 1, 0) over(partition by serialno order by year||month) as filed41,
8 lag(year, 1, 0) over(partition by serialno order by year||month) as year1,
9 lag(month, 1, 0) over(partition by serialno order by year||month) as month1
10 from test1
11 where year || month >= to_char(add_months(sysdate,-2),'yyyymm')
12 and year || month <= to_char(add_months(sysdate,-1),'yyyymm')
13 )
14 where year || month = to_char(add_months(sysdate,-1),'yyyymm')
15 /SERIAL FILE FILE FILE FILE YEAR MONT SERIALNO1 FILE FILE FILE FILE YEAR MONT
------ ---- ---- ---- ---- ---- ---- ---------- ---- ---- ---- ---- ---- ----
累计1 1 2 3 4 2006 12 累计1 55 55 55 55 2006 11
累计2 11 22 33 44 2006 12 累计2 66 66 66 66 2006 11全部都加上新增的系列字段就可以了
------ ---- ---- ---- ---- ---- ----
累计1 1 2 3 4 2006 10
累计2 11 22 33 44 2006 12
累计1 55 55 55 55 2006 11
累计2 66 66 66 66 2006 11SQL> select serialno, to_char(filed1) as filed1, to_char(filed2) as filed2,
2 to_char(filed3) as filed3, to_char(filed4) as filed4, year, month,
3 serialno1, filed11, filed21, filed31, filed41, year1, month1
4 from (
5 select serialno, filed1, filed2, filed3, filed4, year, month,
6 lag(serialno, 1, 0) over(partition by serialno order by year||month) as serialno1,
7 lag(filed1, 1, 0) over(partition by serialno order by year||month) as filed11,
8 lag(filed2, 1, 0) over(partition by serialno order by year||month) as filed21,
9 lag(filed3, 1, 0) over(partition by serialno order by year||month) as filed31,
10 lag(filed4, 1, 0) over(partition by serialno order by year||month) as filed41,
11 lag(year, 1, 0) over(partition by serialno order by year||month) as year1,
12 lag(month, 1, 0) over(partition by serialno order by year||month) as month1
13 from test1
14 where year || month >= to_char(add_months(sysdate,-2),'yyyymm')
15 and year || month <= to_char(add_months(sysdate,-1),'yyyymm')
16 )
17 where year || month = to_char(add_months(sysdate,-1),'yyyymm')
18 union all
19 select '', '', '', '', '', '', '',
20 serialno as serialno1, filed1 as filed11, filed2 as filed21,
21 filed3 as filed31, filed4 as filed41, year as year1, month as month1
22 from test1 a
23 where year || month = to_char(add_months(sysdate,-2),'yyyymm')
24 and not exists (select serialno from test1 b where a.serialno = b.serialno
25 and b.year || b.month = to_char(add_months(sysdate,-1),'yyyymm')
26 )
27 ;SERIAL FILE FILE FILE FILE YEAR MONT SERIALNO1 FILE FILE FILE FILE YEAR MONT
------ ---- ---- ---- ---- ---- ---- ---------- ---- ---- ---- ---- ---- ----
累计2 11 22 33 44 2006 12 累计2 66 66 66 66 2006 11
累计1 55 55 55 55 2006 11
(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, tab_month,
--要求显示上个月的字段
lag(lj_dcsw , 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_dcsw1 , --加上partition by
lag(lj_zr , 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_zr1 ,
lag(lj_zr_qz , 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_zr_qz1 ,
lag(lj_zc , 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_zc1 ,
lag(lj_sdl , 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_sdl1 ,
lag(lj_sdl_qz, 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_sdl_qz1,
lag(lj_xsdl , 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_xsdl1 ,
lag(lj_xsl , 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_xsl1 ,
lag(lj_xsl_ys, 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_xsl_ys1
--要求显示上上个月的字段
from table_name
where tab_year || tab_month >= to_char(add_months(sysdate,-2),'yyyymm')
and tab_year || tab_month <= to_char(add_months(sysdate,-1),'yyyymm')
)
where tab_year || tab_month = 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 || tab_month = to_char(add_months(sysdate,-2),'yyyymm')
and not exists (select 1 from table_name b where a.系列字段 = b.系列字段
and b.tab_year || b.tab_month = to_char(add_months(sysdate,-1),'yyyymm')
2. 系列字段是哪个,麻烦楼主也给出来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, tab_month,
--要求显示上个月的字段
lag(lj_dcsw , 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_dcsw1 , --加上partition by
lag(lj_zr , 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_zr1 ,
lag(lj_zr_qz , 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_zr_qz1 ,
lag(lj_zc , 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_zc1 ,
lag(lj_sdl , 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_sdl1 ,
lag(lj_sdl_qz, 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_sdl_qz1,
lag(lj_xsdl , 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_xsdl1 ,
lag(lj_xsl , 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_xsl1 ,
lag(lj_xsl_ys, 1, 0) over(partition by 系列字段 order by tab_year||tab_month) as lj_xsl_ys1
--要求显示上上个月的字段
from table_name
where tab_year || tab_month >= to_char(add_months(sysdate,-2),'yyyymm')
and tab_year || tab_month <= to_char(add_months(sysdate,-1),'yyyymm')
)
where tab_year || tab_month = 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 || tab_month = to_char(add_months(sysdate,-2),'yyyymm')
and not exists
(select 1 from table_name b where a.系列字段 = b.系列字段
and b.tab_year || b.tab_month = to_char(add_months(sysdate,-1),'yyyymm')
------ ---- ---- ---- ---- ---- ----
累计1 1 2 3 4 2006 10
累计2 11 22 33 44 2006 12
累计1 55 55 55 55 2006 11
累计2 66 66 66 66 2006 11SQL> select serialno, to_char(filed1) as filed1, to_char(filed2) as filed2,
2 to_char(filed3) as filed3, to_char(filed4) as filed4, year, month,
3 serialno1, filed11, filed21, filed31, filed41, year1, month1
4 from (
5 select serialno, filed1, filed2, filed3, filed4, year, month,
6 lag(serialno, 1, 0) over(partition by serialno order by year||month) as serialno1,
7 lag(filed1, 1, 0) over(partition by serialno order by year||month) as filed11,
8 lag(filed2, 1, 0) over(partition by serialno order by year||month) as filed21,
9 lag(filed3, 1, 0) over(partition by serialno order by year||month) as filed31,
10 lag(filed4, 1, 0) over(partition by serialno order by year||month) as filed41,
11 lag(year, 1, 0) over(partition by serialno order by year||month) as year1,
12 lag(month, 1, 0) over(partition by serialno order by year||month) as month1
13 from test1
14 where year || month >= to_char(add_months(sysdate,-2),'yyyymm')
15 and year || month <= to_char(add_months(sysdate,-1),'yyyymm')
16 )
17 where year || month = to_char(add_months(sysdate,-1),'yyyymm')
18 union all
19 select '', '', '', '', '', '', '',
20 serialno as serialno1, filed1 as filed11, filed2 as filed21,
21 filed3 as filed31, filed4 as filed41, year as year1, month as month1
22 from test1 a
23 where year || month = to_char(add_months(sysdate,-2),'yyyymm')
24 and not exists (select serialno from test1 b where a.serialno = b.serialno
25 and b.year || b.month = to_char(add_months(sysdate,-1),'yyyymm')
26 )
27 ;SERIAL FILE FILE FILE FILE YEAR MONT SERIALNO1 FILE FILE FILE FILE YEAR MONT
------ ---- ---- ---- ---- ---- ---- ---------- ---- ---- ---- ---- ---- ----
累计2 11 22 33 44 2006 12 累计2 66 66 66 66 2006 11
累计1 55 55 55 55 2006 11完全符合楼主的要求,不清楚楼主说的是什么情况,楼主不愿意给出实际的数据,
根据楼主例子的数据写出的语句请楼主看清楚再说
(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 || b.lpad(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')