delete from t where (select count(1) from t a where a.编号=t.编号 and datediff(minute,a.时间,t.时间)=0 and a.时间<t.时间)>3go select top 1 id,asee from table where ...... order by id
select count(*) as num,编号,分数,时间,substring(时间,4,2) as fen into #a from tablename group by 编号,分数,时间 declare @i int select @i=max(num) from #a set rowcount @i select id=identity(int,1,1),* into #b from #a set rowcount 0delete tablename from tablename a,#b where a.编号=b.编号 and b.num>3
问题1: delete from t a where a.时间> (select min(时间) from (select top 3 * from t b where convert(char(19),b.时间)=convert(char(19),a.时间)order by 时间) m1 )
select top 1 id,asee from table select id,asee from table group id order by id create table #table(www int not null identity(1,1),id int,asee int) goinsert into #table select 2,10 union select 1,10 goinsert into #table select 1,12 union select 2,11 union select 2,10 union select 2,12 goselect distinct * from ( select a.id,(select top 1 asee from (select top 2 asee from #table bb where a.id=bb.id order by asee) b order by asee desc) as second from #table a group by a.id ) aa
create table #table(www int not null identity(1,1),id int,asee int) goinsert into #table select 2,10 union select 1,10 goinsert into #table select 1,12 union select 2,11 union select 2,10 union select 2,12 goselect * from #table /*顯示原始數據 1 1 10 2 2 10 3 1 12 4 2 10 5 2 11 6 2 12 */select * from #table order by 2,3 /*顯示原始排序數據 1 1 10 3 1 12 4 2 10 2 2 10 5 2 11 6 2 12 select distinct * from ( select a.id,(select top 1 asee from (select top 2 asee from #table bb where a.id=bb.id order by asee) b order by asee desc) as second from #table a group by a.id ) aa order by 1 /*顯示結果 1 12 2 10 */
上面好像錯了,用下面: create table #table(www int not null identity(1,1),id int,asee int) goinsert into #table select 2,10 union select 1,10 goinsert into #table select 1,12 union select 2,11 union select 2,10 union select 2,12 goselect * from #table /*顯示原始數據 1 1 10 2 2 10 3 1 12 4 2 10 5 2 11 6 2 12 */select * from #table order by 2,3 /*顯示原始排序數據 1 1 10 3 1 12 4 2 10 2 2 10 5 2 11 6 2 12 select distinct * from ( select a.id,(select top 1 asee from (select distinct top 2 asee from #table bb where a.id=bb.id order by asee) b order by asee desc) as second from #table a group by a.id ) aa order by 1 /*顯示結果 1 12 2 11 */
where (select count(1) from t a
where a.编号=t.编号 and datediff(minute,a.时间,t.时间)=0 and a.时间<t.时间)>3go select top 1 id,asee
from table
where ......
order by id
declare @i int
select @i=max(num) from #a
set rowcount @i
select id=identity(int,1,1),* into #b from #a
set rowcount 0delete tablename from tablename a,#b where a.编号=b.编号 and b.num>3
delete from t a
where a.时间>
(select min(时间) from (select top 3 * from t b where convert(char(19),b.时间)=convert(char(19),a.时间)order by 时间) m1 )
select id,asee from table group id
order by id create table #table(www int not null identity(1,1),id int,asee int)
goinsert into #table
select 2,10 union
select 1,10
goinsert into #table
select 1,12 union
select 2,11 union
select 2,10 union
select 2,12
goselect distinct * from
(
select a.id,(select top 1 asee from (select top 2 asee from #table bb where a.id=bb.id order by asee) b order by asee desc) as second from #table a group by a.id
) aa
goinsert into #table
select 2,10 union
select 1,10
goinsert into #table
select 1,12 union
select 2,11 union
select 2,10 union
select 2,12
goselect * from #table
/*顯示原始數據
1 1 10
2 2 10
3 1 12
4 2 10
5 2 11
6 2 12
*/select * from #table order by 2,3
/*顯示原始排序數據
1 1 10
3 1 12
4 2 10
2 2 10
5 2 11
6 2 12
select distinct * from
(
select a.id,(select top 1 asee from (select top 2 asee from #table bb where a.id=bb.id order by asee) b order by asee desc) as second from #table a group by a.id
) aa order by 1
/*顯示結果
1 12
2 10
*/
create table #table(www int not null identity(1,1),id int,asee int)
goinsert into #table
select 2,10 union
select 1,10
goinsert into #table
select 1,12 union
select 2,11 union
select 2,10 union
select 2,12
goselect * from #table
/*顯示原始數據
1 1 10
2 2 10
3 1 12
4 2 10
5 2 11
6 2 12
*/select * from #table order by 2,3
/*顯示原始排序數據
1 1 10
3 1 12
4 2 10
2 2 10
5 2 11
6 2 12
select distinct * from
(
select a.id,(select top 1 asee from (select distinct top 2 asee from #table bb where a.id=bb.id order by asee) b order by asee desc) as second from #table a group by a.id
) aa order by 1
/*顯示結果
1 12
2 11
*/