字段1 字段2 字段3
aaa bbb 1
bbb aaa 2
aaa ccc 3
ccc ddd 4怎样得到:
aaa bbb 1
aaa ccc 3
ccc ddd 4
或
bbb aaa 2
aaa ccc 3
ccc ddd 4就是筛选字段1,字段2相同的记录
aaa bbb 1
bbb aaa 2
aaa ccc 3
ccc ddd 4怎样得到:
aaa bbb 1
aaa ccc 3
ccc ddd 4
或
bbb aaa 2
aaa ccc 3
ccc ddd 4就是筛选字段1,字段2相同的记录
insert into tb values('aaa', 'bbb', 1)
insert into tb values('bbb', 'aaa', 2)
insert into tb values('aaa', 'ccc', 3)
insert into tb values('ccc', 'ddd', 4)
goselect t.* from tb t where not exists(select 1 from tb where c1 = t.c2 and c2 = t.c1 and c3 < t.c3)
/*
c1 c2 c3
---------- ---------- -----------
aaa bbb 1
aaa ccc 3
ccc ddd 4(所影响的行数为 3 行)
*/select t.* from tb t where not exists(select 1 from tb where c1 = t.c2 and c2 = t.c1 and c3 > t.c3)
/*
c1 c2 c3
---------- ---------- -----------
bbb aaa 2
aaa ccc 3
ccc ddd 4(所影响的行数为 3 行)
*/drop table tb