如下表,连续字段a相同的记录,取第一条表内容:
id a crDate
1 112.50 2013-05-01 08:10:01.000
2 112.50 2013-05-01 08:10:11.000
5 100.60 2013-05-01 08:15:01.000
8 100.60 2013-05-01 08:20:01.000
13 100.60 2013-05-01 08:21:01.000
26 112.50 2013-05-01 08:22:01.000
35 112.50 2013-05-01 08:25:01.000
40 90.50 2013-05-01 08:40:01.000
41 90.50 2013-05-01 08:42:01.000
52 90.50 2013-05-01 08:42:41.000
55 100.60 2013-05-01 08:50:01.000
70 100.60 2013-05-01 08:51:01.000
71 100.60 2013-05-01 08:53:01.000
72 100.60 2013-05-01 08:55:01.000
77 90.50 2013-05-01 09:03:01.000
78 90.50 2013-05-01 09:04:01.000需要结果:
1 112.50 2013-05-01 08:10:01.000
5 100.60 2013-05-01 08:15:01.000
26 112.50 2013-05-01 08:22:01.000
40 90.50 2013-05-01 08:40:01.000
55 100.60 2013-05-01 08:50:01.000
77 90.50 2013-05-01 09:03:01.000
create table #t
(id int, a decimal(8,2), crDate datetime )insert into #t
select 1, 112.5, '2013-05-01 08:10:01' union all
select 2, 112.5, '2013-05-01 08:10:11' union all
select 5, 100.6, '2013-05-01 08:15:01' union all
select 8, 100.6, '2013-05-01 08:20:01' union all
select 13, 100.6, '2013-05-01 08:21:01' union all
select 26, 112.5, '2013-05-01 08:22:01' union all
select 35, 112.5, '2013-05-01 08:25:01' union all
select 40, 90.5, '2013-05-01 08:40:01' union all
select 41, 90.5, '2013-05-01 08:42:01' union all
select 52, 90.5, '2013-05-01 08:42:41' union all
select 55, 100.6, '2013-05-01 08:50:01' union all
select 70, 100.6, '2013-05-01 08:51:01' union all
select 71, 100.6, '2013-05-01 08:53:01' union all
select 72, 100.6, '2013-05-01 08:55:01' union all
select 77, 90.5, '2013-05-01 09:03:01' union all
select 78, 90.5, '2013-05-01 09:04:01'
id a crDate
1 112.50 2013-05-01 08:10:01.000
2 112.50 2013-05-01 08:10:11.000
5 100.60 2013-05-01 08:15:01.000
8 100.60 2013-05-01 08:20:01.000
13 100.60 2013-05-01 08:21:01.000
26 112.50 2013-05-01 08:22:01.000
35 112.50 2013-05-01 08:25:01.000
40 90.50 2013-05-01 08:40:01.000
41 90.50 2013-05-01 08:42:01.000
52 90.50 2013-05-01 08:42:41.000
55 100.60 2013-05-01 08:50:01.000
70 100.60 2013-05-01 08:51:01.000
71 100.60 2013-05-01 08:53:01.000
72 100.60 2013-05-01 08:55:01.000
77 90.50 2013-05-01 09:03:01.000
78 90.50 2013-05-01 09:04:01.000需要结果:
1 112.50 2013-05-01 08:10:01.000
5 100.60 2013-05-01 08:15:01.000
26 112.50 2013-05-01 08:22:01.000
40 90.50 2013-05-01 08:40:01.000
55 100.60 2013-05-01 08:50:01.000
77 90.50 2013-05-01 09:03:01.000
create table #t
(id int, a decimal(8,2), crDate datetime )insert into #t
select 1, 112.5, '2013-05-01 08:10:01' union all
select 2, 112.5, '2013-05-01 08:10:11' union all
select 5, 100.6, '2013-05-01 08:15:01' union all
select 8, 100.6, '2013-05-01 08:20:01' union all
select 13, 100.6, '2013-05-01 08:21:01' union all
select 26, 112.5, '2013-05-01 08:22:01' union all
select 35, 112.5, '2013-05-01 08:25:01' union all
select 40, 90.5, '2013-05-01 08:40:01' union all
select 41, 90.5, '2013-05-01 08:42:01' union all
select 52, 90.5, '2013-05-01 08:42:41' union all
select 55, 100.6, '2013-05-01 08:50:01' union all
select 70, 100.6, '2013-05-01 08:51:01' union all
select 71, 100.6, '2013-05-01 08:53:01' union all
select 72, 100.6, '2013-05-01 08:55:01' union all
select 77, 90.5, '2013-05-01 09:03:01' union all
select 78, 90.5, '2013-05-01 09:04:01'
select * from #t order by a
SELECT id,a,crdate FROM dbo.TBSELECT id ,
a ,
crdate
FROM @T
WHERE iid IN ( SELECT ( SELECT MAX(iid)
FROM @T AS B
WHERE B.iid <= A.iid
AND b.a = a.a
AND NOT EXISTS ( SELECT *
FROM @T AS C
WHERE B.iid - 1 = C.iid
AND B.a = C.a )
) AS grp
FROM @T AS A )/*
id a crdate
1 112 2013-05-01 08:10:01.000
5 100 2013-05-01 08:15:01.000
26 112 2013-05-01 08:22:01.000
40 90 2013-05-01 08:40:01.000
55 100 2013-05-01 08:50:01.000
77 90 2013-05-01 09:03:01.000*/
go
create table #t
(id int, a decimal(8,2), crDate datetime )
insert into #t
select 1, 112.5, '2013-05-01 08:10:01' union all
select 2, 112.5, '2013-05-01 08:10:11' union all
select 5, 100.6, '2013-05-01 08:15:01' union all
select 8, 100.6, '2013-05-01 08:20:01' union all
select 13, 100.6, '2013-05-01 08:21:01' union all
select 26, 112.5, '2013-05-01 08:22:01' union all
select 35, 112.5, '2013-05-01 08:25:01' union all
select 40, 90.5, '2013-05-01 08:40:01' union all
select 41, 90.5, '2013-05-01 08:42:01' union all
select 52, 90.5, '2013-05-01 08:42:41' union all
select 55, 100.6, '2013-05-01 08:50:01' union all
select 70, 100.6, '2013-05-01 08:51:01' union all
select 71, 100.6, '2013-05-01 08:53:01' union all
select 72, 100.6, '2013-05-01 08:55:01' union all
select 77, 90.5, '2013-05-01 09:03:01' union all
select 78, 90.5, '2013-05-01 09:04:01'
SELECT *
FROM #t AS t
WHERE NOT EXISTS ( SELECT 1
FROM #t AS b
WHERE b.a = t.a
AND b.id < t.id
AND NOT EXISTS ( SELECT 1
FROM #t AS c
WHERE c.a != t.a
AND c.id < t.id
AND c.id > b.id ) )
/*
id a crDate
1 112.50 2013-05-01 08:10:01.000
5 100.60 2013-05-01 08:15:01.000
26 112.50 2013-05-01 08:22:01.000
40 90.50 2013-05-01 08:40:01.000
55 100.60 2013-05-01 08:50:01.000
77 90.50 2013-05-01 09:03:01.000
*/
BEGIN
create table #test(id int,a decimal(8,2),createTime datetime)
declare @a_before decimal(8,2), --保存上一条数据的a
@id int,--这一条数据的id
@a decimal(8,2), --这一条数据的a
@crDate datetime ----这一条数据的crDate
set @a_before = '-1314.15926' --第一次循环时没有@a_before,初始化一个数据(这个数据绝对不会重复有木有)
declare Ctemp cursor local for select id,a,crDate from test
open Ctemp
fetch next from Ctemp into @id,@a,@crDate
while(@@fetch_status=0)
begin
if(@a!=@a_before)--和上一条数据不一样
begin
set @a_before = @a
insert into #test(id,a,createTime) values
(@id,@a_before,@crDate)
end
fetch next from Ctemp into @id,@a,@crDate
end
close Ctemp
deallocate Ctemp
select * from #test
END
(id int, a decimal(8,2), crDate datetime )
insert into #t
select 1, 112.5, '2013-05-01 08:10:01' union all
select 2, 112.5, '2013-05-01 08:10:11' union all
select 5, 100.6, '2013-05-01 08:15:01' union all
select 8, 100.6, '2013-05-01 08:20:01' union all
select 13, 100.6, '2013-05-01 08:21:01' union all
select 26, 112.5, '2013-05-01 08:22:01' union all
select 35, 112.5, '2013-05-01 08:25:01' union all
select 40, 90.5, '2013-05-01 08:40:01' union all
select 41, 90.5, '2013-05-01 08:42:01' union all
select 52, 90.5, '2013-05-01 08:42:41' union all
select 55, 100.6, '2013-05-01 08:50:01' union all
select 70, 100.6, '2013-05-01 08:51:01' union all
select 71, 100.6, '2013-05-01 08:53:01' union all
select 72, 100.6, '2013-05-01 08:55:01' union all
select 77, 90.5, '2013-05-01 09:03:01' union all
select 78, 90.5, '2013-05-01 09:04:01'
select identity(int,1,1) 'rn',id,a,crDate into #t2 from #tselect x.id,x.a,x.crDate
from #t2 x
left join #t2 y on x.rn=y.rn+1
where y.rn is null or x.a<>y.a/*
id a crDate
----------- --------------------------------------- -----------------------
1 112.50 2013-05-01 08:10:01.000
5 100.60 2013-05-01 08:15:01.000
26 112.50 2013-05-01 08:22:01.000
40 90.50 2013-05-01 08:40:01.000
55 100.60 2013-05-01 08:50:01.000
77 90.50 2013-05-01 09:03:01.000(6 row(s) affected)
*/
create table #t
(id int, a decimal(8,2), crDate datetime )insert into #t
select 1, 112.5, '2013-05-01 08:10:01' union all
select 2, 112.5, '2013-05-01 08:10:11' union all
select 5, 100.6, '2013-05-01 08:15:01' union all
select 8, 100.6, '2013-05-01 08:20:01' union all
select 13, 100.6, '2013-05-01 08:21:01' union all
select 26, 112.5, '2013-05-01 08:22:01' union all
select 35, 112.5, '2013-05-01 08:25:01' union all
select 40, 90.5, '2013-05-01 08:40:01' union all
select 41, 90.5, '2013-05-01 08:42:01' union all
select 52, 90.5, '2013-05-01 08:42:41' union all
select 55, 100.6, '2013-05-01 08:50:01' union all
select 70, 100.6, '2013-05-01 08:51:01' union all
select 71, 100.6, '2013-05-01 08:53:01' union all
select 72, 100.6, '2013-05-01 08:55:01' union all
select 77, 90.5, '2013-05-01 09:03:01' union all
select 78, 90.5, '2013-05-01 09:04:01'
--select id,a,crdate from #t t where not exists(select 1 from #t where a=t.a and crDate<t.crDate)
go
if OBJECT_ID('tab1') is not null
drop table tab1
create table tab1(id int,a decimal(8,2),crdate datetime)
declare @id int,@a decimal(8,2),@crdate datetime
declare mycursor cursor for select * from #t
open mycursor
fetch next from mycursor into @id,@a,@crdate
while (@@FETCH_STATUS=0)
begin
if exists(select 1 from tab1 where a=@a and id=(select MAX(id) from tab1))
begin
fetch next from mycursor into @id,@a,@crdate
end
else
insert into tab1 values(@id,@a,@crdate)
end
close mycursor
deallocate mycursor
go
select * from tab1