请问下这个sql在
select BLDD from(select BLDD,YWDJRQ from POP_MARRIAGE where (ZFID=t.RKBSID or qzid =t.RKBSID) order by YWDJRQ desc)where rownum =1)as
P_CREATE_DIVISION
这个地方报t.RKBSID标识符无效是什么问题?表POP_PERSON是有RKBSID字段的。
sql语句如下:
select
t.RKBSID as GUID,
t.XM as PERSON_NAME,
t.XB as GENDER,
t1.JKZK as HEALTH_STATE,
t.ZJLX as IDENTITY_TYPE,
t.ZJHM as IDENTITY_NUMBER,
t.CSRQ as BIRTHDAY,
t.MZ as ETHNIC_GROUP,
t.WHCD as EDUCATION_LEVEL,
t.GJ as NATIONALITY,
t.ZZMM as POLITICAL_LANDSCAPE,
t1.DSZNFMGRZBH as GLORIOUS_PERMIT_NUMBER,
t.SFDS as ONLY_CHILD,
t.LXDH as CONTACT_NUMBER,
t.ZY as EMPLOYMENT_STATUS,
t.GZDW as WORK_UNIT,
t.HYZK as MARITAL_STATUS,
t.HYBDRQ as CHANGE_DATE,
t.POBSID as P_GUID,
t.POXM as P_PERSON_NAME,
t.POGMSFHM as P_IDENTITY_NUMBER,
t.XJZD as LIVING_DIVISION,
t.XJZDXZ as LIVING_ADDRESS,
t.HJDXZ as HOUSEHOLD_ADDRESS,
t.SFBJQC as MARK_MOVE_OUT,
t.SFBJLK as MARK_MOVE_EXIT,
t.GDSJSFWZZJHM as PROVINCIAL_CERT_NUMBER,
t.GDSJSFWZLZRQ as PROVINCIAL_CERT_ISSUE_DATE,
t.LRBDRQ as MOVE_IN_DATE,
t.LCBDRQ as MOVE_OUT_DATE,
t.ZXYY as CANCEL_REASON,
t.ZXRQ as CANCEL_DATE,
t.LDZHM as NATIONAL_CERT_NUMBER,
t.LLDZRQ as NATIONAL_CERT_ISSUE_DATE,
t.LDZYXRQ as NATIONAL_CERT_EFFECTIVE_DATE,
t.JKRQ as move_CREATE_TIME,
t.JKR as move_CREATE_USER,
t.GXRQ as LAST_UPDATE_TIME,
(select xm from POP_PERSON where rkbsid=t1.QSMQBSID)as M_PERSON_NAME,
(select xm from POP_PERSON where rkbsid=t1.QSFQBSID)as F_PERSON_NAME,
(select CSRQ from POP_PERSON where rkbsid=t1.QSMQBSID)as M_BIRTHDAY,
(select CSRQ from POP_PERSON where rkbsid=t1.QSFQBSID)as F_BIRTHDAY,
(select ZJHM from POP_PERSON where rkbsid=t1.QSMQBSID)as M_IDENTITY_NUMBER,
(select ZJHM from POP_PERSON where rkbsid=t1.QSFQBSID)as F_IDENTITY_NUMBER,
t1.YWDJR as CREATE_DIVISION,
t1.YWGXRQ as CREATE_TIME,
t1.YWGXR as CREATE_USER,
(select CSRQ from POP_PERSON where rkbsid=t.POBSID)as P_BIRTHDAY,
(select HYZK from POP_PERSON where rkbsid=t.POBSID)as P_MARITAL_STATUS,
(select HYBDRQ from POP_PERSON where rkbsid=t.POBSID)as P_CHANGE_DATE,
(select BLDD from(select BLDD,YWDJRQ from POP_MARRIAGE where (ZFID=t.RKBSID or qzid =t.RKBSID) order by YWDJRQ desc)where rownum =1)as
P_CREATE_DIVISION,
(select max(YWDJRQ) from POP_MARRIAGE where ZFID=t.RKBSID or qzid =t.RKBSID)as
P_CREATE_TIME,
(select YWDJR from(select YWDJR,YWDJRQ from POP_MARRIAGE where ZFID=t.RKBSID or qzid =t.RKBSID order by YWDJRQ desc)where rownum =1)as
P_CREATE_USER
from POP_PERSON t left join POP_CHILDREN t1 on t.RKBSID=t1.WYBS;
因为你在子查询里面包了太多层,所以oracle就不认识了呗
试试这个行不行?
(select distinct LAST_VALUE(BLDD) OVER (ORDER BY YWDJRQ ROWS BETWEEN unbounded preceding AND unbounded following) from POP_MARRIAGE where (ZFID=t.RKBSID or qzid =t.RKBSID))as
P_CREATE_DIVISION
详细自己百度:LAST_VALUE()