最近做的一个模块涉及到8张表的联合查询,由于这8张表中有很多主从表的关联,结果在使用模糊查询的时候查询结果集出现了重复记录。如下:
执行的SQL语句如下:
select
cf.id id,
o.OBJECTCODE,
o.OBJECTNAME objNAME1,
cf.name name,
o1.OBJECTNAME objNAME2,
o2.OBJECTNAME objNAME3,
cf.no no,
bc.cert_no,
bc.FIELD3_VALUE,
to_char(bc.CREATE_DATE,'yyyy-MM-dd'),
trim(to_char(nvl(cf.FORMAMOUNT,0),'99,999,999,999,999,990.99')),
o3.OBJECTNAME objNAME4,
ap.PERSONNELNAME apName,
t.actorname,
to_char(cf.CREATETIME,'yyyy-MM-dd'),
cf.TEMPLETEID,
cf.orgentityid
FROM
t_cc_ct_bc bc,
T_CC_BillDetailData detail,
T_CC_BILLMAINDATA main,
cc_form cf,
t_sys_user ap,
T_CC_OBJECT o,
T_CC_OBJECT o1,
T_CC_OBJECT o2,
T_CC_OBJECT o3,
t_sys_flow_task t where 1=1
and cf.no=main.BILLNUMBER
and main.item109=o.OBJECTID
and main.REQUISITIONUSER=ap.USERID
and detail.BILLMAINDATAID=main.BILLMAINDATAID
and main.billMainDataID=bc.BILLMAINDATAID
and o1.OBJECTID=detail.DIMACCOUNT
and o2.OBJECTID=detail.COMPUTATIONACCOUNT
and o3.OBJECTID=main.RequisitionUserDepartment
and main.billnumber=t.bono
and t.tasktype='finishTask'
and t.activityname='共享审核会计'
and (t.ACTORNAME LIKE '%黄%')
order by cf.createtime desc;
一、使用DISTINCT去重
select
DISTINCT(cf.ID),
o.OBJECTCODE,
o.OBJECTNAME objNAME1,
cf.name name,
o1.OBJECTNAME objNAME2,
o2.OBJECTNAME objNAME3,
cf.no no,
bc.cert_no,
bc.FIELD3_VALUE,
to_char(bc.CREATE_DATE,'yyyy-MM-dd'),
trim(to_char(nvl(cf.FORMAMOUNT,0),'99,999,999,999,999,990.99')),
o3.OBJECTNAME objNAME4,
ap.PERSONNELNAME apName,
t.actorname,
to_char(cf.CREATETIME,'yyyy-MM-dd') as cfcreatetime,
cf.TEMPLETEID,
cf.orgentityid
FROM
t_cc_ct_bc bc,
T_CC_BillDetailData detail,
T_CC_BILLMAINDATA main,
cc_form cf,
t_sys_user ap,
T_CC_OBJECT o,
T_CC_OBJECT o1,
T_CC_OBJECT o2,
T_CC_OBJECT o3,
t_sys_flow_task t
where 1=1 and cf.no=main.BILLNUMBER
and main.item109=o.OBJECTID
and main.REQUISITIONUSER=ap.USERID
and detail.BILLMAINDATAID=main.BILLMAINDATAID
and main.billMainDataID=bc.BILLMAINDATAID
and o1.OBJECTID=detail.DIMACCOUNT
and o2.OBJECTID=detail.COMPUTATIONACCOUNT
and o3.OBJECTID=main.RequisitionUserDepartment
and main.billnumber=t.bono
and t.tasktype='finishTask'
and t.activityname='共享审核会计'
and (t.ACTORNAME LIKE '%刘%')
order by cfcreatetime desc
这里就不过多的解释了,在这里我们使用ID去重,只需要将ID字段使用DISTINCT()处理一下就可以了。
注意,在这之前遇到一个问题:
这是由于DISTINCT函数和ORDER BY冲突导致的,从上面的SQL中可以看出,最后是要根据某一表的日期字段进行排序的,之前我用的是cf.createtime,引发了上面的sql错误,后来将cf.createtime这一列添加了一个别名,就这样,问题完美的解决了,然后再ORDER BY的时候使用别名,这样就完美的解决了。
to_char(cf.CREATETIME,'yyyy-MM-dd') as cfcreatetime
二、使用row_number() over (partition order by)去重
with ect as(
select
cf.id id,
o.OBJECTCODE,
o.OBJECTNAME objNAME1,
cf.name name,
o1.OBJECTNAME objNAME2,
o2.OBJECTNAME objNAME3,
cf.no no,
bc.cert_no,
bc.FIELD3_VALUE,
to_char(bc.CREATE_DATE,'yyyy-MM-dd'),
trim(to_char(nvl(cf.FORMAMOUNT,0),'99,999,999,999,999,990.99')),
o3.OBJECTNAME objNAME4,
ap.PERSONNELNAME apName,
t.actorname,
to_char(cf.CREATETIME,'yyyy-MM-dd') as create1,
cf.TEMPLETEID,
cf.orgentityid
FROM
t_cc_ct_bc bc,
T_CC_BillDetailData detail,
T_CC_BILLMAINDATA main,
cc_form cf,
t_sys_user ap,
T_CC_OBJECT o,
T_CC_OBJECT o1,
T_CC_OBJECT o2,
T_CC_OBJECT o3,
t_sys_flow_task t where 1=1
and cf.no=main.BILLNUMBER
and main.item109=o.OBJECTID
and main.REQUISITIONUSER=ap.USERID
and detail.BILLMAINDATAID=main.BILLMAINDATAID
and main.billMainDataID=bc.BILLMAINDATAID
and o1.OBJECTID=detail.DIMACCOUNT
and o2.OBJECTID=detail.COMPUTATIONACCOUNT
and o3.OBJECTID=main.RequisitionUserDepartment
and main.billnumber=t.bono
and t.tasktype='finishTask'
and t.activityname='共享审核会计'
and (t.ACTORNAME LIKE '%黄%'))
select * from(
select ss.*,row_number() over (partition by id order by create1) rid from ect ss
) a where rid=1
说明:
1.with ect as()
括号内的内容,就是查询的sql结果,其中包含重复数据,于是用with as() 创建一个临时表ect为临时表的名字。
2.select ss.*,row_number() over (partition by id order by create1) rid from ect ss
这句sql的意思是,查询临时表ect 别名为ss,row_number() over(partition by 需要检索重复的列 order by 排序的列名) 别名为 rid form ect ss,这时候就会查询获得一个rid列,如果id列存在多条相同值就以1开始递增。
3.select * from (↑) a where rid = 1
这句sql是筛选rid为1,也就是id只出现1次的数据,这时候就去重复了。
最后执行结果如下;
从ID上看,我们是去重成功的。