SQL脚本-查询一个表内相同纪录 having

  • A+
所属分类:网站建设
摘要

如果一个ID可以区分的话,可以这么写select * from 表 where ID in (

如果一个ID可以区分的话,可以这么写

select * from 表 where ID in (
select ID from 表 group by ID having sum(1)>1)

如果几个ID才能区分的话,可以这么写

select * from 表 where ID1+ID2+ID3 in
(select ID1+ID2+ID3 from 表 group by ID1,ID2,ID3 having sum(1)>1)

其他回答:数据表是zy_bho,想找出ZYH字段名相同的记录

--方法1:    
SELECT  *FROM  zy_bho  a  WHERE  EXISTS 
  (SELECT  1  FROM  zy_bho  WHERE  [PK]  <>  a.[PK]  AND  ZYH  =  a.ZYH) 
   
--方法2:
select  a.*  from  zy_bho  a  join  zy_bho  b 
      on  (a.[pk]<>b.[pk]  and  a.zyh=b.zyh) 
   
--方法3:
select  *  from  zy_bbo  where  zyh  in 
(select  zyh  from  zy_bbo  group  by  zyh  having  count(zyh)>1) 
--其中pk是主键或是 unique的字段。
广告也精彩

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: