一个关于sql语句查询的难题(查询一个列中(比如name)姓名相同并且个数大于1的name的语句).解决思路_Sql Server

一个关于sql语句查询的难题(查询一个列中(比如name)姓名相同并且个数大于1的name的语句).
比如有一个表student
id name pwd
1 zhang zhang
2 zhang qq
3 zhen wog
4 zhen gg
5 zhen wogs
6 hu meifa

比如这个student这个表中, name这一列有2个zhang,3个zhen, 1个qi.
我如何使用sql语句查询出姓名个数大于1个的name啊!!!
这个问题困扰了我,希望各位大师帮帮我,谢谢啊.

------解决方案--------------------

SQL code

select namefrom studentgroup by namehaving count(*) > 1
------解决方案--------------------
SQL codeselect *from student twhere exists (select 1 from student where name = t.name and id <> t.id)
------解决方案--------------------
SQL codeselect namefrom studentgroup by namehaving max(id) > min(id)
------解决方案--------------------
select name from student group by name having count(1) > 1

select * from student where name in (select name from student group by name having count(1) > 1)
  
------解决方案--------------------
select name ,num  from  
(select name,num = count(name) from tb group by name)t where num >1
------解决方案--------------------
SQL codeselect * from student Awhere EXISTS (select name from student WHERE A.name = name group by name having count(1) > 1)
------解决方案--------------------
AcHerat 的性能发面比较好，建议采用这个
------解决方案--------------------
select name from student group by name having count(*) > 1

having count(*)>是对group by 后生成的虚拟表的数据进行过滤，过滤掉只有一个的情况，剩下的就是需要查询的了