数据查询统计的问题

Question

组与人员的关系表，是多对多的关系。一个组可以对应多个人员，一个人员也可以对应多个组，现在有这样一个需求。要在这个表中查出有关联的组，即只要某个组中所包含的人员在另一个组中存在的话就把这些相关的组找出来，请问这个语句怎么写。表结构如下：             人员编号                    组编号
1 F3302000000002007121294 A3302000000002007120835
2 F3302000000002007121294 A3302000000002007120845
3 F3302000000002007121295 A3302000000002007120836
4 F3302000000002007121295 A3302000000002007120842
5 F3302000000002007121295 A3302000000002007120846
6 F3302000000002007121296 A3302000000002007120836
7 F3302000000002007121296 A3302000000002007120837
8 F3302000000002007121296 A3302000000002007120842
9 F3302000000002007121297 A3302000000002007120837
10 F3302000000002007121298 A3302000000002007050004
11 F3302000000002007121298 A3302000000002007120837
12 F3302000000002007121301 A3302021700002005080004
13 F3302000000002007121304 A3302000303002005060002
14 F3302000000002007121306 A3302000000002007120850
15 F3302000000002007121306 A3302810000002005080001
16 F3302000000002007121307 A3302826700002005090012
17 F3302000000002007121308 A3302000000002007050004
18 F3302000000002007121309 A3302270000002006090003
19 F3302000000002007121310 A3302270000002006090003
20 F3302000000002007121311 A3302270000002006090003
21 F3302000000002007121312 A3302000000002007120843
22 F3302000000002007121312 A3302000000002007120846
23 F3302000000002007121313 A3302000000002007050004
24 F3302000000002007121315 A3302000000002007120847
25 F3302000000002007121316 A3302000000002007120844
26 F3302000000002007121316 A3302000000002007120845
27 F3302000000002007121316 A3302000000002008010857
28 F3302000000002007121320 A3302000000002007120848
29 F3302000000002007121321 A3302000000002007120849
30 F3302000000002007121322 A3302000000002007120850
31 F3302000000002008011326 A3302000000002007120847
32 F3302000000002008011326 A3302000000002007120852
33 F3302000000002008011326 A3302000000002008020870

Answer 1

select 人员编号, 组编号 from 表where 
人员编号 in(
select a.人员编号
from 
from (
select 人员编号,count(组编号) count
from 表
group by 人员编号) a
where a.count>1)

Answer 2

WITH a AS (SELECT '1' ID,'aa' dept FROM dual
          UNION
           SELECT '1' ID,'bb' dept FROM dual
          UNION
           SELECT '3' ID,'bb' dept FROM dual
          UNION
           SELECT '4' ID, 'cc' dept FROM dual
          UNION
           SELECT '4' ID,'dd' dept FROM dual
          UNION
           SELECT '5' ID,'dd' dept FROM dual
             )
SELECT ID
FROM a 
GROUP BY ID
HAVING COUNT(dept) > 1

Answer 3

为什么
WITH a AS (SELECT '1' ID,'aa' dept FROM dual
          UNION
           SELECT '1' ID,'bb' dept FROM dual
          UNION
           SELECT '3' ID,'bb' dept FROM dual
          UNION
           SELECT '4' ID, 'cc' dept FROM dual
          UNION
           SELECT '4' ID,'dd' dept FROM dual
          UNION
           SELECT '5' ID,'dd' dept FROM dual
             )
SELECT ID
FROM a 
GROUP BY ID没数据出来啊？