去重复,and 条件是被去记录的某个值;
比如
select * from TB a where id in (select max(id) from TB where a.BB=BB and c=5)
意思是把BB重复的记录变成一条,同时满足,被去记录的c=5
比如
select * from TB a where id in (select max(id) from TB where a.BB=BB and c=5)
意思是把BB重复的记录变成一条,同时满足,被去记录的c=5
调试欢乐多
1 12 5
2 12 0
3 13 6
4 13 4
.....要结果是 2 12 0,不要 4 13 4
or
select top 1 * from TB a where id in (select max(id) from TB where a.BB=BB and c=5)
insert into tb select 1,12,5
insert into tb select 2,12,0
insert into tb select 3,13,6
insert into tb select 4,13,4
insert into tb select 5,14,2
insert into tb select 6,14,5
select * from tb where id in(
select max(id) from tb where bb in
(select bb from tb where c=5) group by bb)id bb c
2 12 0
6 14 5我明白了
2,12,0
5,14,2
insert into tb select 1,12,5
insert into tb select 2,12,0
insert into tb select 3,13,6
insert into tb select 4,13,4
insert into tb select 5,14,2
insert into tb select 6,14,5select a.*
from tb a
join (select bb from tb where c=5) b
on a.bb=b.bb and a.c!=5drop table tb/*
id bb c
----------- ----------- -----------
2 12 0
5 14 2(2 行受影响)
*/
select tb.* from tb,
(Select max(t.id) as id,t.bb from tb t where exists(select 1 from tb where c=5 and t.bb=tb.bb ) and c<>5 group by bb) s
where tb.id=s.id and tb.bb=s.bb--Resultsid bb c
----------- ----------- -----------
2 12 0
5 14 2
create table tb(id int,bb int,c int)
insert into tb select 1,12,5
insert into tb select 2,12,0
insert into tb select 3,13,6
insert into tb select 4,13,4
insert into tb select 5,14,2
insert into tb select 6,14,5
insert into tb select 7,15,5
insert into tb select 1,12,5
insert into tb select 2,12,0
insert into tb select 3,13,6
insert into tb select 4,13,4
insert into tb select 5,14,2
insert into tb select 6,14,5
insert into tb select 6,14,3
insert into tb select 7,15,5select max(id) id,a.bb,max(c) c
from tb a
join (select bb from tb where c=5) b
on a.bb=b.bb and a.c!=5
group by a.bbdrop table tb/*
id bb c
----------- ----------- -----------
2 12 0
6 14 3
*/
(Select max(t.id) as id,t.bb from tb t where exists(select 1 from tb where c=5 and t.bb=tb.bb ) and c<>5 group by bb) s
where tb.id=s.id and tb.bb=s.bb
意思是把BB重复的记录变成一条,同时满足,被去记录的c=5-----这个理论上产生的条件多条返回记录是,a表和c表的都有BB字段,而且BB字段是主键成分, 但是,a表或者C表有主键约束不是1个列,很可能是2个列或者多个列组成的.所以你根据A.BB=BB 可能有是普通连接产生多个返回记录 解决的方法:select distinct(AA) from tb a where id in (select max(id) from TB where a.BB=BB and c=5)
AA 的意思就是同样BB下对应不同的值的列