例: name1 name2 name3 name4 name5
aaa bbb ccc ddd fg
aaa bbb ccc ddd ff
aaa bbb ccc ddd cc
aa bb cc dd ee
aa bb cc dd aa
bb aa cc dd ff
cc dd cc cc dd-- 要求:查询显示所有(name1,name2,name3,name4)字段中含有重复内容的字段.
结果为: name1 name2 name3 name4 name5
aaa bbb ccc ddd fg
aaa bbb ccc ddd ff
aaa bbb ccc ddd cc
aa bb cc dd ee
aa bb cc dd aa
aaa bbb ccc ddd fg
aaa bbb ccc ddd ff
aaa bbb ccc ddd cc
aa bb cc dd ee
aa bb cc dd aa
bb aa cc dd ff
cc dd cc cc dd-- 要求:查询显示所有(name1,name2,name3,name4)字段中含有重复内容的字段.
结果为: name1 name2 name3 name4 name5
aaa bbb ccc ddd fg
aaa bbb ccc ddd ff
aaa bbb ccc ddd cc
aa bb cc dd ee
aa bb cc dd aa
>where exists(select * from tb where a,name1=name1 and a.name2=name2 and a.name3=name3 and a.name4=name4 and a.name5<>name5)
多谢feixianxxx 大哥 回复 不过.这性能太低了!
有性能高点的吗? 我的数据量大概有1000W。。用你这查询会死人的。。
HELP。。
mysql> select * from test T where exists (select 1 from test where name1=t.name1 and name2=t.name2 and name3=t.name3 and name4=t.name4 and name5<>t.name5);
+-------+-------+-------+-------+-------+
| name1 | name2 | name3 | name4 | name5 |
+-------+-------+-------+-------+-------+
| aaa | bbb | ccc | ddd | fg |
| aaa | bbb | ccc | ddd | ff |
| aaa | bbb | ccc | ddd | cc |
| aa | bb | cc | dd | ee |
| aa | bb | cc | dd | aa |
+-------+-------+-------+-------+-------+
inner join
(select name1,name2,name3,name4 from tt group by name1,name2,name3,name4 having count(*)>1) b
on a.name1=b.name1 and a.name2=b.name2 and a.name3=b.name3 and a.name4=b.name4
from tb a join
(select name1,name2,name3,name4
from tb
group by name1,name2,name3,name4
having count(name5)>1 )k
on a.name1=k.name1 and a.name2=k.name2 and a.name3=k.name3 and a.name4=k.name4
给name1 name2 name3 name4 加上索引会快很多的