有这样的数据 table Aid name
1 a
2 b
3 c
4 c
5 d
6 a
7 e
8 e
9 f
.
.
数据还有很多,没有规律
需要查出这样的结果,按顺序把相同的合并成一个,只要相临的2个值相同就去掉重复的。a
b
c
d
a
e
f
1 a
2 b
3 c
4 c
5 d
6 a
7 e
8 e
9 f
.
.
数据还有很多,没有规律
需要查出这样的结果,按顺序把相同的合并成一个,只要相临的2个值相同就去掉重复的。a
b
c
d
a
e
f
where not exists(select 1 from tb where id=a.id+1 and name=a.name)
select *
from tb A
where not exits (select 1 from tb A.id=id-1 and a.name=B.name)
declare @A table (id int,name varchar(1))
insert into @A
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'c' union all
select 5,'c' union all
select 6,'d' union all
select 7,'a' union all
select 8,'e' union all
select 9,'e' union all
select 10,'f'
select name from @A a
where not exists(select 1 from @A b where id=a.id+1 and name=a.name)
/*
name
----
a
b
c
d
a
e
f
*/
(select temp2.id from a as temp1
left join a temp2 on temp1.id = temp2.id+1 and temp1.name=temp2.name where temp2.id is not null)
INSERT #temp
select '1', 'a' union all
select '2', 'b' union all
select '3', 'c' union all
select '4', 'c' union all
select '5', 'd' union all
select '6', 'a' union all
select '7', 'e' union all
select '8', 'e' union all
select '9', 'f'
GO
--sql:
SELECT [name] FROM
(
SELECT
rowno=ROW_NUMBER() OVER(ORDER BY GETDATE()),
groupno=ROW_NUMBER() OVER(PARTITION BY [name] ORDER BY GETDATE()),
*
FROM #temp
) T
GROUP BY [name], rowno-groupno
ORDER BY MIN(rowno)
/*
a
b
c
d
a
e
f
*/
where exists (select * from a tb where tb.id=a.id+1 and a.name=tb.name)