查询人员表中同一个业务中重复人员ID的数据:
WITH temp AS (
SELECT a.personID||’_’||a.BUSINESSID AS bus FROM(
select n.personID,b.BUSINESSID from KY_PERSON n,KY_PERSON_RELATION b WHERE
n.ITEMID = b.ITEMID AND
n.PERSONID in (
select m.PERSONID from KY_PERSON m
group by m.PERSONID
having count(m.PERSONID) > 1
)
ORDER BY personID
) a
)
SELECT n.bus FROM temp n WHERE bus IN (
select m.bus from temp m
group by m.bus
having count(m.bus) > 1
)
查询指定字段重复的数据:
select n.personID from KY_PERSON n WHERE
n.PERSONID in (
select m.PERSONID from KY_PERSON m
group by m.PERSONID
having count(m.PERSONID) > 1
)