本例测试环境是EXCEL中用VBA连接ACCESS数据库
有如下一张表,要分别查找出所有同名的人、所有同名并且学号也一样的人以及所有同名但不同学号的人。
查询所有同名人员
select * from [18年考试成绩] where [姓名] in (SELECT [姓名] FROM [18年考试成绩] group by [姓名] having count(姓名)>1)
查询结果:
查找所有姓名和学号两个字段都重复的人
select * from [18年考试成绩] where [姓名] in (SELECT [姓名] FROM [18年考试成绩] group by [姓名] having count(姓名)>1) and [学号] in (SELECT [学号] FROM [18年考试成绩] group by [学号] having count(学号)>1)
查询结果:
查找所有姓名重复但学号不重复的人
select * from [18年考试成绩] where [姓名] in (SELECT [姓名] FROM [18年考试成绩] group by [姓名] having count(姓名)>1) and [学号] not in (SELECT [学号] FROM [18年考试成绩] group by [学号] having count(学号)>1)
注意:要把SELECT查询子句配合in或not in 操作符使用,SELECT查询子句只能有一列内容。