如下表
id weight height birth name
1 90 160 1991-1-1
2 80 150 1992-1-1
3 90 160 1991-1-1
4 100 150 1992-2-1
....我想查出weight,height和birth字段值均一样的重复数据
如果是一个字段,我知道用group by weight having count(weight)>1
但是现在3个字段,应该如何写sql,请指教
id weight height birth name
1 90 160 1991-1-1
2 80 150 1992-1-1
3 90 160 1991-1-1
4 100 150 1992-2-1
....我想查出weight,height和birth字段值均一样的重复数据
如果是一个字段,我知道用group by weight having count(weight)>1
但是现在3个字段,应该如何写sql,请指教
group by weight,height,birth having count(*)>1
不知道理解有没错。
select a.* from tb a,tb b
where a.weight = b.weight
and a.height = b.height
and a.birth = b.birth
and a.id <> b.id
/*
id,weight,height,birth
1,90,160,1991-1-1
3,90,160,1991-1-1(2 行受影响)