create table test(
tid int auto_increment primary key,
fid int,
tname varchar(10)
)insert into test(fid,tname)values(20,'ROA');
insert into test(fid,tname)values(20,'ROB');
insert into test(fid,tname)values(21,'ROA');
insert into test(fid,tname)values(21,'ROB');
insert into test(fid,tname)values(22,'ROA');数据库:mysql
表的结构,数据如上。
要列出fid为20,21的记录,剔除fid为22的数据。即没有重复值的不要。谢谢
tid int auto_increment primary key,
fid int,
tname varchar(10)
)insert into test(fid,tname)values(20,'ROA');
insert into test(fid,tname)values(20,'ROB');
insert into test(fid,tname)values(21,'ROA');
insert into test(fid,tname)values(21,'ROB');
insert into test(fid,tname)values(22,'ROA');数据库:mysql
表的结构,数据如上。
要列出fid为20,21的记录,剔除fid为22的数据。即没有重复值的不要。谢谢
from test a
where exists (select fid from test where fid=a.fid and tid!=a.tid);select a.*
from test a inner join (select fid from test group by fid having count(*)>1) b
on a.fid=b.fid;select *
from test
where fid in (select fid from test group by fid having count(*)>1);三种皆可,效率在不同环境下不同。
我还是差很远啊,要继续努力!!