首先求表2中人數用
select count(*) from (select 姓名 from 表2 group by 姓名) a
然后 表1與表2關聯并按模號分組,找出關聯后次數等于人數的模塊。
即﹕
select a.模號 from 表1 a inner join 表2 b on a.模號=b.模號 group by a.模號
having count(*)=(select count(*) from (select 姓名 from 表2 group by 姓名) a )
select 模号
from 表A A
where not exists(select 1 from
(select * from (
(select distinct 姓名 from 表B) B,(select 模号 from 表A ) C
) D
left join 表B E
on D.姓名=E.姓名 and D.模号=E.模号
where D.模号=A.模号 and E.模号 is not null)