22,300
社区成员




create table tb(ID varchar(6))
insert into tb select '123456'
insert into tb select '123123'
insert into tb select '123456'
insert into tb select '123456'
insert into tb select '123789'
go
set nocount on
--重构基础临时表
select identity(int,1,1)as idt,* into # from tb
select '1.'
select * from # a where not exists(select 1 from # where id=a.id and idt<a.idt)
select '2.'
select * from # a where not exists(select 1 from # where id=a.id and idt<>a.idt)
select '3.1'
select * from # a where not exists(select 1 from # where id=a.id group by id having count(*)>2)
select '3.2'
select * from # a where not exists(select 1 from # where id=a.id group by id having count(*)>4)
select '4.1'
select * from # where left(id,3)='123'
select '4.2'
select * from # where left(id,4)='1234'
go
drop table tb,#
/*
----
1.
idt ID
----------- ------
1 123456
2 123123
5 123789
----
2.
idt ID
----------- ------
2 123123
5 123789
----
3.1
idt ID
----------- ------
2 123123
5 123789
----
3.2
idt ID
----------- ------
1 123456
2 123123
3 123456
4 123456
5 123789
----
4.1
idt ID
----------- ------
1 123456
2 123123
3 123456
4 123456
5 123789
----
4.2
idt ID
----------- ------
1 123456
3 123456
4 123456
*/