聚合函数下查询多个列

查询聚合函数符合某个条件的记录，只能查询聚合的列，不能查询其他列，例如查询手机号出现一次以上的记录，只能下面这样

select Phone,count(phone)
from PeisPatient group by Phone having count(phone)>1

 

加上其他列就不行

select PatientCode,Phone,count(phone),IDCardNo,PatientName,ID_Patient
from PeisPatient group by Phone,PatientCode,IDCardNo,PatientName,ID_Patient having count(phone)>1

但可以用个性能不太好的语句实现：

select PatientCode,IDCardNo,PatientName,ID_Patient,phone from PeisPatient where phone in
(
select Phone
from PeisPatient group by Phone having count(phone)>1 ) order by phone