34,575
社区成员
发帖
与我相关
我的任务
分享
drop table t1
create table t1 (mytext varchar(10))
insert into t1
select 'a' union all
select 'a' union all
select 'a' union all
select 'b' union all
select 'c' union all
select 'c' union all
select 'b' union all
select 'a'
drop table #
select id=identity(int,1,1),* into # from t1
select mytext
from # a
where not exists(select * from # where mytext=a.mytext and id=a.id+1)
--> 测试数据: #
if object_id('tempdb.dbo.#') is not null drop table #
create table # (name varchar(10))
insert into #
select 'a' union all
select 'a' union all
select 'a' union all
select 'b' union all
select 'c' union all
select 'c' union all
select 'b' union all
select 'a'
if object_id('tempdb.dbo.#T') is not null drop table #T
select id=identity(int,1,1),*,cn=cast(null as int) into #T from #
declare @name varchar(10),@i int
select @i = 0
update #T set
cn = case when name = @name then @i else @i+1 end,
@i = case when name = @name then @i else @i+1 end,
@name = name
select name from #T a where id = (select max(id) from #T where cn=a.cn)
/*
name
----------
a
b
c
b
a
*/
drop table t1
create table t1(
mytext varchar(5)
)
insert into t1
select 'A'
union all select 'A'
union all select 'A'
union all select 'C'
union all select 'C'
union all select 'C'
union all select 'B'
union all select 'B'
union all select 'D'
select *
from t1
select distinct mytext
from t1
--结果:
/*
mytext
------
A
A
A
C
C
C
B
B
D
(所影响的行数为 9 行)
mytext
------
A
B
C
D
(所影响的行数为 4 行)
*/