==============
看起来很简单,可是想了几个感觉都不好
Time IDinTime Data
1 1 0 A
2 1 1 C
3 1 2 F
4 2 3 B
5 2 5 A
6 2 8 F
7 3 1 A
8 3 9 F
9 4 0 D
10 4 5 S
11 4 8 F
12 4 9 A
. . . .
. . . .
. . . .Time代表记录的添加时间,简单起见用数字代表,由一个时间内可能会插入N个记录(即使时间精确到ms级),为了区别同一时间的记录,又加了id in time这个字段,用来区别.id in time 是递增的,但不保证每一个新的时间都从0开始,只保证同一时间内的值是递增的问题是,我想求第五行及第五行以后的数据,如何做呢?(即同一个时间点内从中间隔开,取以后的数据),效率越高越好(我想Time + id of time 转成字符串,然后求>Time + id of time ,可是,这样如果数据量很大的话,很浪费资源....)注:数据表已经是order by Time asc, IdinTime asc了
===========大家有好主意帮帮我啊
(
select px=(select count(1) from tb where Time=a.Time and IDinTime<a.IDinTime)+1 , * from tb a
) t
where px >= 5
order by time,idintime
麻烦大家再看一下,===========
条件:(时间点大于第五行的) or (如果时间点等于第五行,且id in time 大于第五行id in time)
Select * From TB X Where Exists
(Select 1 From (
Select
*
From (
Select Time,IDinTime,
(Select Time From TB
Where Time=A.Time And IDinTime<=A.IDinTime
Group By Time Having Count(1)<5) As [Count]
From TB A
) T
Where Count Is Null
) Y
Where X.Time=Y.Time And X.IDinTime=Y.IDInTime)
最好只用SELECT
麻烦了,dawugui(潇洒老乌龟)
最好只用SELECT
麻烦了,dawugui(潇洒老乌龟)不行.Time 有重复,IDinTime 有重复,Data 有重复,没法区分大小.
的=============SoftwKLC(自由的飞鸟) ,我研究下,看有没有什么启发:)
select * form table where id > N
create table #tab(Time int,IDinTime int,Data varchar(5))
insert #tab(Time,IDinTime,Data)
select '1','0','A' union all
select '1','1','C' union all
select '1','2','F' union all
select '2','3','B' union all
select '2','5','A' union all
select '2','8','F' union all
select '3','1','A' union all
select '3','9','F' union all
select '4','0','D' union all
select '4','5','S' union all
select '4','8','F' union all
select '4','9','A'
go
--执行测试语句
select t.Time,t.IDinTime,t.Data
from #tab t
where not exists(
select 1
from(select top 5 * from #tab order by time,IDinTime) x
where x.time = t.time and x.IDinTime = t.IDinTime
)
go
--删除测试环境
drop table #tab
go
/*--测试结果
Time IDinTime Data
----------- ----------- -----
2 8 F
3 1 A
3 9 F
4 0 D
4 5 S
4 8 F
4 9 A(所影响的行数为 7 行)
*/
你试一下这个
declare @t table([desc] int,id int)
insert into @t
select 1,1
union all select 1,2
union all select 3,3
union all select 1,22
union all select 5,2222
union all select 6,22
union all select 7,2
union all select 8,2
union all select 9,2
union all select 10,2
select * from @t where checksum([desc],id)not in (select top 3 checksum([desc],id) from @t )
根据你讲的我的意思,一个时间如果有大于5条或以上的记录则从
第5条开始取直到该时间的最后一条,如果某时间记录少于5条
则取不到*/
---创建测试环境
Declare @T Table(Time int,IDinTime int,Data Varchar(10))
Insert @T Select 1,0,'A'
Union All Select 1,1,'C'
Union All Select 1,2,'F'
Union All Select 1,3,'F'
Union All Select 1,5,'F' ---时间1的第5条
Union All Select 1,6,'F' ---时间1的第6条
Union All Select 1,9,'F' ---时间1的第7条
Union All Select 2,3,'B'
Union All Select 2,5,'A'
Union All Select 2,8,'F'
Union All Select 3,1,'A'
Union All Select 3,9,'F'
Union All Select 4,0,'D'
Union All Select 4,5,'S'
Union All Select 4,8,'F'
Union All Select 4,9,'A'
Union All Select 4,10,'X' ---时间4的第5条
Union All Select 4,12,'Y' ---时间4的第6条Select * From @T
---查询结果(不知道LZ你是否这样意思??)
Select * From @T X Where Exists
(Select 1 From (
Select
*
From (
Select Time,IDinTime,
(Select Time From @T
Where Time=A.Time And IDinTime<=A.IDinTime
Group By Time Having Count(1)<5) As [Count]
From @T A
) T
Where Count Is Null
) Y
Where X.Time=Y.Time And X.IDinTime=Y.IDInTime)
---结果
/*
Time IDinTime Data
----------- ----------- ----------
1 5 F
1 6 F
1 9 F
4 10 X
4 12 Y(所影响的行数为 5 行)
*/
我的问题还在思考中......
即便楼主就是这个意思,也不用像你写的那般复杂。
Declare @T Table(Time int,IDinTime int,Data Varchar(10))
Insert @T Select 1,0,'A'
Union All Select 1,1,'C'
Union All Select 1,2,'F'
Union All Select 1,3,'F'
Union All Select 1,5,'F' ---时间1的第5条
Union All Select 1,6,'F' ---时间1的第6条
Union All Select 1,9,'F' ---时间1的第7条
Union All Select 2,3,'B'
Union All Select 2,5,'A'
Union All Select 2,8,'F'
Union All Select 3,1,'A'
Union All Select 3,9,'F'
Union All Select 4,0,'D'
Union All Select 4,5,'S'
Union All Select 4,8,'F'
Union All Select 4,9,'A'
Union All Select 4,10,'X' ---时间4的第5条
Union All Select 4,12,'Y' ---时间4的第6条---查询结果
Select * From @T X
Where not Exists(
select 1
from (select top 4 * from @t where time = x.time order by IDinTime) y
where x.time = y.time and x.IDinTime = y.IDinTime
)
/*
Time IDinTime Data
----------- ----------- ----------
1 5 F
1 6 F
1 9 F
4 10 X
4 12 Y(所影响的行数为 5 行)
*/