34,593
社区成员
发帖
与我相关
我的任务
分享
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([value1] int,[value2] int)
insert [tb]
select 1,12 union all
select 1,13 union all
select 1,23 union all
select 0,14 union all
select 0,15 union all
select 1,16 union all
select 0,23 union all
select 0,22 union all
select 1,21 union all
select 1,12
结果:
value1 value2 col3
----------- ----------- -----------
1 12 1
1 13 2
1 23 3
0 14 1
0 15 2
1 16 1
0 23 1
0 22 2
1 21 1
1 12 2
可以写下代码
你的回答: if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([value1] int,[value2] int)
insert [tb]
select 1,12 union all
select 1,13 union all
select 1,23 union all
select 0,14 union all
select 0,15 union all
select 1,16 union all
select 0,23 union all
select 0,22 union all
select 1,21 union all
select 1,12
select id=identity(int,1,1),* into # from tb
select
value1,value2,
col3=
(
select
count(1)
from
#
where
[value1]=a.[value1]
and
id<=a.id
and
id>=(select isnull(max(id),0) from # where id<a.id and value1!=a.value1))
from
# a
--结果:
/*
value1 value2 col3
----------- ----------- -----------
1 12 1
1 13 2
1 23 3
0 14 1
0 15 2
1 16 1
0 23 1
0 22 2
1 21 1
1 12 2
*/
select * from tb t
where not exists(select null from tb where t.value=value and t.TimeStamp<TimeStamp)
select * from tb t where =(select min(TimeStamp) from tb where value=t.value)
union all
select * from tb t where =(select max(TimeStamp) from tb where value=t.value)