如何去掉连续的重复的字段

[wd_6532]

1 a
2 a
3 a
4 b
5 c
6 c
7 d

结果是
1 a
4 b
5 c
7 d

请问怎么实现

[让你望见影子的墙]

select min (id),name
from tb 
group by name

[ChinaJiaBing]

declare @a table (id int,name varchar(10))
insert into @a select 1,'a'
     union all select 2,'a'
     union all select 3,'a'
     union all select 4,'b'
     union all select 5,'c'
     union all select 6,'c'
     union all select 7,'d'
 select * from @a a where
  not exists (select 1 from @a where id<a.id and name=a.name)

id          name
----------- ----------
1           a
4           b
5           c
7           d

(4 行受影响)

[百年树人]

select
  min(col1) as col1,
  col2
from tb
group by col2

[让你望见影子的墙]

[Quote=引用楼主 wd_6532 的帖子:]
1 a
2 a
3 a
4 b
5 c
6 c
7 d

结果是
1 a
4 b
5 c
7 d

请问怎么实现
[/Quote]

楼主：
1 a
2 a
3 a
4 b
5 c
6 c
7 d
8 a --那么这个a显示吗？

[長胸為富]

select * from #a a where col1 in (select min(col1) from #a where col2 = a.col2)

[wd_6532]

4楼问的好，是我在1楼没说清楚。


楼主：
1 a
2 a
3 a
4 b
5 c
6 c
7 d
8 a --那么这个a显示吗？


a也显示。

[百年树人]

select 
  *
from 
  tb t
where 
  not exists(select 1 from tb where col2=t.col2 and col1=t.col1-1)

[百年树人]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col1] int,[col2] varchar(1))
insert [tb]
select 1,'a' union all
select 2,'a' union all
select 3,'a' union all
select 4,'b' union all
select 5,'c' union all
select 6,'c' union all
select 7,'d' union all
select 8,'a'

select 
  *
from 
  tb t
where 
  not exists(select 1 from tb where col2=t.col2 and col1=t.col1-1)


--测试结果:
/*
col1        col2 
----------- ---- 
1           a
4           b
5           c
7           d
8           a

（所影响的行数为 5 行）

*/

[让你望见影子的墙]

写的比较麻烦了

-->> Author:  让你望见影子的墙(HEROWANG)生成测试数据  Date:2009-04-18 20:20:24
IF OBJECT_ID('tb') IS NOT NULL 
    DROP TABLE tb
Go
CREATE TABLE tb(id INT,name NVARCHAR(1))
Go
INSERT INTO tb
SELECT 1,'a' UNION ALL
SELECT 2,'a' UNION ALL
SELECT 3,'a' UNION ALL
SELECT 4,'b' UNION ALL
SELECT 5,'c' UNION ALL
SELECT 6,'c' UNION ALL
SELECT 7,'a' UNION ALL
SELECT 8,'d' 
GO

SELECT 	* FROM	TB
select id,name
from (
select *,row=(select count(*)+1 from tb where name=t.name and id<t.id and name=(select name from tb where id=t.id-1))
from tb t)k
where row=1

1	a
4	b
5	c
7	a
8	d

[htl258_Tony]

if object_id('tb') is not null drop table tb
go
create table tb(id int,name varchar(10))
insert tb select 1,'a'
union all select 2,'a'
union all select 3,'a'
union all select 4,'b'
union all select 5,'c'
union all select 6,'c'
union all select 7,'d'
union all select 8,'a'

alter table tb add fid int

declare @i int,@name varchar(10)
update tb
set fid=@i,
    @i=case 
          when name=@name then isnull(@i,0)
          else isnull(@i,0)+1
        end,
    @name=name

select id,name from tb t where not exists(select 1 from tb where name=t.name and fid=t.fid and id<t.id)

/*
id          name
----------- ----------
1           a
4           b
5           c
7           d
8           a

(5 行受影响)
*/
alter table tb drop column fid

[htl258_Tony]

[Quote=引用 8 楼 josy 的回复:]
SQL codeif object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col1] int,[col2] varchar(1))
insert [tb]
select 1,'a' union all
select 2,'a' union all
select 3,'a' union all
select 4,'b' union all
select 5,'c' union all
select 6,'c' union all
select 7,'d' union all
select 8,'a'

select
*
from
tb t
where
not exists(select 1 from tb where col2=t.col2 and c…
[/Quote]这个不错。

[wd_6532]

4楼，思想深邃

8楼，正解。

谢谢各位。