sql语句问题 ( 积分: 100 )

已知数据表如下:
日期 分数
2007-1-1 1
2007-1-1 2
2007-1-1 3
2007-1-1 4
2007-1-2 1
2007-1-2 3
2007-1-2 8
2007-1-2 5
2007-1-3 1
2007-1-3 1
2007-1-3 9
......

如何找出按照日期把每天的前3名找出来形成一个新的表呢?

dtime 日期,DATA分数,kkkk为楼主认为的基本表
select dtime,max(data)as data,1 as mm from kkkk group by dtime
union
select m.dtime,max(m.data)as data,2 from(select s.* ,case when s.data=m.data then 1 else 0 end tt from kkkk s left join (select dtime,max(data)as data from kkkk group by dtime ) m on s.dtime=m.dtime and s.data=m.data)m where isnull(m.tt,0)=0
group by m.dtime
union
select m.dtime,max(m.data)as data,3 from (select s.* case when s.data=m.data then 1 else 0 end tt from kkkk s left join (select dtime,max(data)as data from kkkk group by dtime
union
select m.dtime,max(m.data) as data from (select s.*,case when s.data=m.data then 1 else 0 end tt from kkkk s left join (select dtime,max(data) as data from kkkk group by dtime)m on s.dtime=m.dtime and s.data=m.data
where isnull(m.tt,0)=0
group by m.dtime)m
on s.dtime=m.dtime and s.data=m.data) m
where isnull(m.tt,0)=0
group by m.dtime

用个存储过程写
declare @i int,@maxdate datetime,@mindate datetime;
select @mindate=max(日期) from 表名;
select @maxdate=max(日期) from 表名;
WHILE (@mindate < @maxdate)
begin
DATEADD(Day,1,@mindate);
select top 3 * into 新表名 from 表名 where 日期 = order by 分数 desc;
end;

不知道行不行,楼主试一下,写得急,没来得急试

select top 3 * into NewTableName from TableName order by 分数 desc

表table1(d,s):

select *
from table1 X
where X.s >= (
select min(Y.s)
from (select top 3 * from table1 where d= X.d order by s desc) AS Y)
order by X.d,X.s desc

xxj的代码简单可用,我用的是太复杂了,以后我改用你这种方式

接受答案了.