Oracle 数据重复问题
查询的数据中包含clob类型的数据，用distinct或group by无法去重，用to_char转换不够大，求各位大神指点
select * from (
select vd.id as doorid, /*文档ID*/
vd.docsubject as subject, /*文档标题*/
vd.doccontent as doccontent, /*文档内容*/
vd.createdatetime as datecreated, /*创建日期*/
r.orgid,r.parentid from v_docdetail vd inner join doc_sharedetail s on(vd.id=s.docid) inner join orgunitrelate r on(s.orgid=r.orgid)
where vd.docstatus not in(0,5,6) and r.orgid in(select orgid from orgunitrelate start with orgid=183 connect by prior parentid=orgid)
union all
select vd.id as doorid, /*文档ID*/
vd.docsubject as subject, /*文档标题*/
vd.doccontent as doccontent, /*文档内容*/
vd.createdatetime as datecreated, /*创建日期*/
null orgid,null parentid from v_docdetail vd inner join doc_sharedetail s on(vd.id=s.docid)
where vd.docstatus not in(0,5,6) and s.isalluser=1 or s.userid=1658
union all
select vd.id as doorid, /*文档ID*/
vd.docsubject as subject, /*文档标题*/
vd.doccontent as doccontent, /*文档内容*/
vd.createdatetime as datecreated, /*创建日期*/
null orgid,null parentid from v_docdetail vd where vd.docstatus not in(0,5,6) and 1=1658
union all
select * from (
select vd.id as doorid, /*文档ID*/
vd.docsubject as subject, /*文档标题*/
vd.doccontent as doccontent, /*文档内容*/
vd.createdatetime as datecreated, /*创建日期*/
re.childid orgid,re.roleid parentid from v_docdetail vd inner join doc_sharedetail s on( vd.docstatus not in(0,5,6) and vd.id=s.docid)
inner join hrmrolerelation re on(s.roleid=re.roleid)) START WITH orgid in (select roleid from hrmrolemembers
where resourceid=1658) CONNECT BY PRIOR orgid=parentid
union all
select * from (select * from (
select vd.id as doorid, /*文档ID*/
vd.docsubject as subject, /*文档标题*/
vd.doccontent as doccontent, /*文档内容*/
vd.createdatetime as datecreated, /*创建日期*/
re.childid orgid,re.roleid parentid from v_docdetail vd inner join doc_sharedetail s on( vd.docstatus not in(0,5,6) and vd.id=s.docid)
inner join roleorgunitrelation re on(s.roleid=re.roleid)) START WITH orgid in (select roleid from hrmrolemembers
where resourceid=1658) CONNECT BY PRIOR orgid=parentid) where orgid=183)
where (to_char(doorid) like '%文档%' or to_char(doorid) like '%信息%' or to_char(doorid) like '%信息%') or ( subject like '%文档%' or subject like '%信息%') or ( doccontent like '%文档%' or doccontent like '%信息%') order by datecreated desc
