--建立測試環境 create table baseinfo(id int ,name varchar(20))insert into baseinfo select 1,'a' union all select 2,'a' union all select 3,'b' union all select 4,'b' union all select 5,'c' union all select 6,'d' --測試 select B.* from baseinfo A,baseinfo B where A.name=B.name and A.id!=B.id --所影響的結果行數 /* ID name 1 a 2 a 3 b 4 b*/ --刪除測試環境 drop table baseinfo
select * from baseinfo a where (select count(*) from baseinfo where name = a.name) > 1
select distinct a.id,a.url, a.name,a.taskname, b.id,b.url, b.name, b.taskname from companylist as a, companylist as b where a.name = b.name and a.id < b.id
select * from baseinfo a where (select count(*) from baseinfo where name = a.name) > 1
create table baseinfo(id int ,name varchar(20))insert into baseinfo
select 1,'a' union all
select 2,'a' union all
select 3,'b' union all
select 4,'b' union all
select 5,'c' union all
select 6,'d'
--測試
select B.* from baseinfo A,baseinfo B
where A.name=B.name and A.id!=B.id
--所影響的結果行數
/*
ID name
1 a
2 a
3 b
4 b*/
--刪除測試環境
drop table baseinfo
from baseinfo a
where (select count(*) from baseinfo where name = a.name) > 1
from companylist as a, companylist as b
where a.name = b.name and a.id < b.id
from baseinfo a
where (select count(*) from baseinfo where name = a.name) > 1
from baseinfo
where name in (select name from baseinfo group by name where count(*)>1)
FROM baseinfo
WHERE ((SELECT COUNT(*)
FROM (SELECT *
FROM baseinfo) AS T1
WHERE T1.name=baseinfo.name) > 1)