1。用游标循环每一个ID 2。然后取出每一个ID的第10条数据,并插入临时表 insert into #table select top 1 * from (select top 10 * from yourtable where [ID]='currentID' order by [value] asc)t order by [value] desc 3。最后从临时表中取出数据
SELECT * FROM TABLE a WHERE ID=(SELECT TOP 1 ID FROM ( select top 10 id from table where id=a.id order by id asc )T WHERE id=a.id order by id desc )
SELECT * FROM TABLE a WHERE ID=(SELECT TOP 1 ID FROM ( select top 10 id from table where id=a.id order by id asc )T WHERE id=a.id order by id desc )
SELECT * FROM TABLE a WHERE ID=(SELECT TOP 1 ID FROM ( select top 10 id from table where name=a.name order by id asc )T WHERE id=a.id order by id desc ) 我找个环境再测试一下
ID NAME NUM 1 3 2 2 3 2 3 3 2 4 4 2 5 4 2 6 4 2 7 5 2 8 5 2 9 5 2SELECT * FROM t1 a WHERE ID=(SELECT TOP 1 ID FROM ( select top 10 id from t1 where name=a.name order by id asc )T WHERE name=a.name order by id desc ) 结果 ID NAME NUM 3 3 2 6 4 2 9 5 2
要生成一个自增ID列,用临时表select identity(int,1,1) as idd ,ID ,Count into #T1 from yourtable SELECT * FROM #T1 a WHERE idd=(SELECT TOP 1 idd FROM ( select top 10 idd from #T1 where ID=a.ID order by idd asc )T WHERE ID=a.ID order by idd desc )
要生成一个自增ID列,用临时表select identity(int,1,1) as idd ,ID ,Count into #T1 from yourtable SELECT * FROM #T1 a WHERE idd=(SELECT TOP 1 idd FROM ( select top 3 idd from #T1 where ID=a.ID order by idd asc )T WHERE ID=a.ID order by idd desc ) 自己变通一下就行了。不然不要学编程了
--在一个表中,有50个ID,每个ID有超过20条记录,取每个ID第10条数据,Helpselect * from ( select *,'idnew'=row_number() over(partition by id order by id) from tb )a where idnew=10
Sorry. --在一个表中,有50个ID,每个ID有超过20条记录,取每个ID第10条数据,Helpselect * from ( select *,'idnew'=row_number() over(partition by id order by col) --col为排序字段 from tb )a where idnew=10
insert into #table select top 1 * from (select top 10 * from yourtable where [ID]='currentID' order by [value] asc)t order by [value] desc 3。最后从临时表中取出数据
SELECT * FROM TABLE a WHERE ID=(SELECT TOP 1 ID FROM ( select top 10 id from table where id=a.id order by id asc )T WHERE id=a.id order by id desc )
SELECT * FROM TABLE a WHERE ID=(SELECT TOP 1 ID FROM
( select top 10 id from table where id=a.id order by id asc )T
WHERE id=a.id order by id desc )
SELECT * FROM TABLE a WHERE ID=(SELECT TOP 1 ID FROM
( select top 10 id from table where name=a.name order by id asc )T
WHERE id=a.id order by id desc )
我找个环境再测试一下
ID NAME NUM
1 3 2
2 3 2
3 3 2
4 4 2
5 4 2
6 4 2
7 5 2
8 5 2
9 5 2SELECT * FROM t1 a WHERE ID=(SELECT TOP 1 ID FROM
( select top 10 id from t1 where name=a.name order by id asc )T
WHERE name=a.name order by id desc )
结果
ID NAME NUM
3 3 2
6 4 2
9 5 2
1 32
1 32
1 3
1 3
1 3
1 2
2 43
2 43
2 465
2 3465
2 546
2 4
2 47
4 7
4 65
4 65
4 657665
8 5
8 7
8 6575
8 65
8 655
9 65
9 7
9 765随便写的例子,取每个ID其中的第3条数据(降序)
要生成一个自增ID列,用临时表select identity(int,1,1) as idd ,ID ,Count into #T1 from yourtable
SELECT * FROM #T1 a WHERE idd=(SELECT TOP 1 idd FROM
( select top 10 idd from #T1 where ID=a.ID order by idd asc )T
WHERE ID=a.ID order by idd desc )
要生成一个自增ID列,用临时表select identity(int,1,1) as idd ,ID ,Count into #T1 from yourtable
SELECT * FROM #T1 a WHERE idd=(SELECT TOP 1 idd FROM
( select top 3 idd from #T1 where ID=a.ID order by idd asc )T
WHERE ID=a.ID order by idd desc )
自己变通一下就行了。不然不要学编程了
(
select *,'idnew'=row_number() over(partition by id order by id)
from tb
)a
where idnew=10
--在一个表中,有50个ID,每个ID有超过20条记录,取每个ID第10条数据,Helpselect * from
(
select *,'idnew'=row_number() over(partition by id order by col) --col为排序字段
from tb
)a
where idnew=10
试试,上面的数据大量都是重的。
declare @t table(ID int, Count int)
insert @t select 1, 32
insert @t select 1, 32
insert @t select 1, 3
insert @t select 1, 3
insert @t select 1, 3
insert @t select 1, 2
insert @t select 2, 43
insert @t select 2, 43
insert @t select 2, 465
insert @t select 2, 3465
insert @t select 2, 546
insert @t select 2, 4
insert @t select 2, 47
insert @t select 4, 7
insert @t select 4, 65
insert @t select 4, 65
insert @t select 4, 657665
insert @t select 8, 5
insert @t select 8, 7
insert @t select 8, 6575
insert @t select 8, 65
insert @t select 8, 655
insert @t select 9, 65
insert @t select 9, 7
insert @t select 9, 765
select distinct id,count
from(
select id,count,cnt=(select count(1)+1 from @t where id=a.id and count>a.count)
from @t a)b
where cnt=3
/*
id count
----------- -----------
1 3
2 465
8 65
9 7
*/