SQL:
第一步–根据group by 分组查询,根据“name”分组查出表中记录:select * from user group by name;
第二步–根据分组查询的结果,筛选出其中分组记录数大于1的”name”:select name from user group by name having count(*)>1;
第三步–根据上一步,使用“in”来包住满足要求的“name”字段的值,并由此查询出最终的结果:select * from user where name in (select name from user group by name having count(*)>1);
素材表数据:user[{“id”:1,”name”:”x”},{“id”:2,”name”:”x”},{“id”:3,”name”:”y”},{“id”:4,”name”:”y”},{“id”:5,”name”:”z”},{“id”:6,”name”:”x”}]
场景:查询出表中”name”重复的所有记录
SQL:
第一步–根据group by 分组查询,根据“name”分组查出表中记录:select * from user group by name;
第二步–根据分组查询的结果,筛选出其中分组记录数大于1的”name”:select name from user group by name having count(*)>1;
第三步–根据上一步,使用“in”来包住满足要求的“name”字段的值,并由此查询出最终的结果:select * from user where name in (select name from user group by name having count(*)>1);