Posted on 2008-11-09 11:24
H2O 閱讀(573)
評論(1) 編輯 收藏 所屬分類:
DataBase
sql語句查詢出重復記錄"
trackback:ping="http://m.tkk7.com/xiaoyi/services/trackbacks/239506.aspx" />
-->
-- 建表
create table a(
id int primary key auto_increment,
contents varchar(32)
);
--插入測試數據
insert into a values(default,'AA');
insert into a values(default,'BB');
insert into a values(default,'AA');
insert into a values(default,'CC');
insert into a values(default,'BB');
insert into a values(default,'DD');
insert into a values(default,'EE');
insert into a values(default,'FF');
insert into a values(default,'FF');
-- 查出所有重復數據
select id,contents from (
select * from (select * from a) aa
inner join
(select id idd,contents cons from a) bb
where aa.id!=bb.idd and aa.contents = bb.cons
) rst order by id asc;
-- 查處重復數據中的一條 如：AA BB FF重復了 只查處一個AA 一個BB 一個FF
select distinct(contents) from (
select id,contents from (
select * from (select * from a) aa
inner join
(select id idd,contents cons from a) bb
where aa.id!=bb.idd and aa.contents = bb.cons
) rst order by id asc
) t;
其他方法1：
select aa.id,aa.contents from a as aa,(select contents from a group by contents HAVING count(*)>1) bb
where aa.contents = bb.contents
其他方法2：
select contents from a as aa where (select count(contents) from a as b where aa.contents = b.contents)>1