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
[解决办法]
[img=http://my.csdn.net/my/album/detail/1308089][/img]
这是查询的数据的截图，求大神们帮帮忙
[解决办法]
第一次发图，上面那个图挂了，请看这个
[解决办法]
应该可以去重吧 是不是有空格什么的？ row_number() over()排序看看
[解决办法]
clob数据要用 dbms_lob.compare来做比较滴。
[解决办法]
我要的不是比较clob里面的内容，我要的是查出来的数据只有一条
[解决办法]
null