SELECT A.COL1,A.COL2,A.COL3 FROM (SELECT A.COL1,A.COL2,A.COL3 FROM TEST A,GROUP B WHERE A.USER_ID = B.USER_ID GROUP BY A.COL1,A.COL2,A.COL3,B.GROUP_ID) A GROUP A.COL1,A.COL2,A.COL3 HAVING COUNT(*)>1
SELECT AA.COL1,AA.COL2,AA.COL3 FROM (SELECT A.COL1,A.COL2,A.COL3 FROM TEST A, GROUP1 B WHERE A.USER_ID = B.USER_ID(+) GROUP BY A.COL1,A.COL2,A.COL3,B.GROUP_ID) AA GROUP AA.COL1,AA.COL2,AA.COL3 HAVING COUNT(*)>1
这样应该可以:select B.COL1||B.COL2||B.COL3 from B minus select B.COL1||B.COL2||B.COL3 from A, B where A.usrid = B.usrid group by groupid having count(*)>1其实可以把B.COL1||B.COL2||B.COL3 看成一个字段来处理~
关键是使用distinctSELECT A.COL1,A.COL2,A.COL3 FROM TEST A,GROUP_tab B WHERE A.USER_ID = B.USER_ID(+) GROUP BY A.COL1,A.COL2,A.COL3 having count(distinct NVL(B.GROUP_ID,0))>1
SELECT A.COL1,A.COL2,A.COL3 FROM TEST A,GROUP_tab B WHERE A.USER_ID = B.USER_ID(+) GROUP BY A.COL1,A.COL2,A.COL3 having count(distinct NVL(B.GROUP_ID,a.user_id))>1
SELECT A.COL1,A.COL2,A.COL3 FROM TEST A,GROUP_tab B WHERE A.USER_ID = B.USER_ID GROUP BY A.COL1,A.COL2,A.COL3 having count(distinct B.GROUP_ID)>1
SELECT A.COL1,A.COL2,A.COL3 FROM TEST A,GROUP_tab B WHERE A.USER_ID = B.USER_ID GROUP BY A.COL1,A.COL2,A.COL3 having count(distinct B.GROUP_ID)>1
FROM
(SELECT A.COL1,A.COL2,A.COL3
FROM TEST A,GROUP B
WHERE A.USER_ID = B.USER_ID
GROUP BY A.COL1,A.COL2,A.COL3,B.GROUP_ID) A
GROUP A.COL1,A.COL2,A.COL3
HAVING COUNT(*)>1
1. user_id有可能不属于任何group(如34)
2. 最好能分步进行的,比如被分配给的多个user_id属于同一个group如何判断??(关键在此)
这么简单的查询 分步进行 不是太浪费了吗
FROM
(SELECT A.COL1,A.COL2,A.COL3
FROM TEST A, GROUP1 B
WHERE A.USER_ID = B.USER_ID(+)
GROUP BY A.COL1,A.COL2,A.COL3,B.GROUP_ID) AA
GROUP AA.COL1,AA.COL2,AA.COL3
HAVING COUNT(*)>1
minus
select B.COL1||B.COL2||B.COL3 from A, B
where A.usrid = B.usrid
group by groupid having count(*)>1其实可以把B.COL1||B.COL2||B.COL3 看成一个字段来处理~
FROM TEST A,GROUP_tab B
WHERE A.USER_ID = B.USER_ID(+)
GROUP BY A.COL1,A.COL2,A.COL3
having count(distinct NVL(B.GROUP_ID,0))>1
FROM TEST A,GROUP_tab B
WHERE A.USER_ID = B.USER_ID(+)
GROUP BY A.COL1,A.COL2,A.COL3
having count(distinct NVL(B.GROUP_ID,a.user_id))>1
FROM TEST A,GROUP_tab B
WHERE A.USER_ID = B.USER_ID
GROUP BY A.COL1,A.COL2,A.COL3
having count(distinct B.GROUP_ID)>1
FROM TEST A,GROUP_tab B
WHERE A.USER_ID = B.USER_ID
GROUP BY A.COL1,A.COL2,A.COL3
having count(distinct B.GROUP_ID)>1