简单查询

[304的的哥]

create table #Record
(
	aID int identity(1,1),
	fID int,
	time datetime
)
insert #Record
select 1,'2010-12-10' union all
select 2,'2010-12-11' union all
select 15,'2010-12-13' union all
select 4,'2010-12-14' union all
select 5,'2010-12-15' union all
select 2,'2010-12-16' union all
select 3,'2010-12-17' union all
select 4,'2010-12-18'
go

--要求：查询出离现在最近的5条记录，且fID不重复(没出现在查询出来的记录里面)
--结果：
aID     fID     time
----------------------------------------
8	4	2010-12-18 00:00:00.000
7	3	2010-12-17 00:00:00.000
6	2	2010-12-16 00:00:00.000
5	5	2010-12-15 00:00:00.000
3	15	2010-12-13 00:00:00.000    

[水族杰纶]

select top 5 *
from tb
order by [time] desc

[bqb]

select top 5 * From #record a where not exists(select 1 from #record where fID=a.fID and time>a.time) order by time desc
/*
aID     fID     time
----------------------------------------
8    4    2010-12-18 00:00:00.000
7    3    2010-12-17 00:00:00.000
6    2    2010-12-16 00:00:00.000
5    5    2010-12-15 00:00:00.000
3    15    2010-12-13 00:00:00.000    

*/

[304的的哥]

[Quote=引用 1 楼 wufeng4552 的回复:]

SQL code
select top 5 *
from tb
order by [time] desc
[/Quote]

这是我写的语句，但是没成功，注意要求：...，且fID不重复(没出现在查询出来的记录里面)
select top(5) * from #Record
order by time desc

[chuifengde]

select 4,'2010-12-14'
select 4,'2010-12-18'

这两条中离现在最近的明显是select 4,'2010-12-14' ，你的结果有问题，或者你的说法不正确

[siegebaoniu]

select top 5 * from
(select max(aid) as aid ,fid,max(time)as t from #record group by fid )a order by [t] desc

/*
8	4	2010-12-18 00:00:00.000
7	3	2010-12-17 00:00:00.000
6	2	2010-12-16 00:00:00.000
5	5	2010-12-15 00:00:00.000
3	15	2010-12-13 00:00:00.000

*/

[304的的哥]

2楼正解！
谢谢各位的及时回答！
看来子查询得好好学学！

[chuifengde]

create table #Record(aID int identity(1,1),fID int,time datetime)
insert #Record
select 1,'2010-12-10' union all
select 2,'2010-12-11' union all
select 15,'2010-12-13' union all
select 4,'2010-12-14' union all
select 5,'2010-12-15' union all
select 2,'2010-12-16' union all
select 3,'2010-12-17' union all
select 4,'2010-12-18'

SELECT TOP 5 WITH TIES  * FROM #Record a 
WHERE NOT EXISTS(SELECT 1 FROM #Record WHERE a.fID=fID AND ABS(DATEDIFF(DAY,a.TIME,GETDATE()))
>ABS(DATEDIFF(DAY,TIME,getdate())))
ORDER BY ABS(DATEDIFF(DAY,TIME,getdate()))

DROP TABLE #Record

--result
/*aID         fID         time                                                   
----------- ----------- ------------------------------------------------------ 
1           1           2010-12-10 00:00:00.000
2           2           2010-12-11 00:00:00.000
3           15          2010-12-13 00:00:00.000
4           4           2010-12-14 00:00:00.000
5           5           2010-12-15 00:00:00.000

（所影响的行数为 5 行）*/