select tm,bm,pm,gg from abc group by tm,bm,pm,gg
select * from abc where (select count(*) from abc a where a.tm=abc.tm and a.bm=abc.bm and a.pm=abc.pm and a.gg=abc.gg)>1
select * from abc where (select count(*) from abc a where a.tm=abc.tm and a.bm=abc.bm and a.pm=abc.pm and a.gg=abc.gg)>1
select a.*
from abc a join(
select sd=min(sd) from abc
group by tm,bm,pm,gg
)b on a.sd=b.sd
--2.
select * from abc a
where exists(
select 1 from abc
where sd<>a.sd
and tm=a.tm and bm=a.bm
and pm=a.pm and gg=a.gg)
use 库名
go
select * distint from abc
select a.*
from abc a join(
select sd=min(sd) from abc
group by tm,bm,pm,gg
)b on a.sd=b.sd
--2.
select * from abc a
where exists(
select 1 from abc
where sd<>a.sd
and tm=a.tm and bm=a.bm
and pm=a.pm and gg=a.gg)
create table abc(sd int,tm int,bm int,pm varchar(10),gg varchar(10))
insert abc select 1,111,111,'ab' ,'abc'
union all select 2,111,111,'ab' ,'abc'
union all select 3,156,222,'kk' ,'l;;'
union all select 4,156,222,'kk' ,'l;;'
union all select 5,984,555,'ssd','d'
go--1.
select a.*
from abc a join(
select sd=min(sd) from abc
group by tm,bm,pm,gg
)b on a.sd=b.sd--2.
select * from abc a
where exists(
select 1 from abc
where sd<>a.sd
and tm=a.tm and bm=a.bm
and pm=a.pm and gg=a.gg)
go--删除测试
drop table abc/*--测试结果
sd tm bm pm gg
----------- ----------- ----------- ---------- ----------
1 111 111 ab abc
3 156 222 kk l;;
5 984 555 ssd d(所影响的行数为 3 行)
sd tm bm pm gg
----------- ----------- ----------- ---------- ----------
1 111 111 ab abc
2 111 111 ab abc
3 156 222 kk l;;
4 156 222 kk l;;(所影响的行数为 4 行)
--*/
SELECT DISTINCT tm,bm,pm,gg
FROM abc
select count(*) from abc
where sd<=a.sd and sd in(
select sd=min(sd) from abc
group by tm,bm,pm,gg))
,a.tm,a.bm,a.pm,a.gg
from abc a join(
select sd=min(sd) from abc
group by tm,bm,pm,gg
)b on a.sd=b.sd