积极答复者
用SQL语查出访问表中最近前几位的访问者?不知怎么做了

问题
-
我有下面一个VisitRecoder表,
列名 数据类型 说明
ID int 主键ID
VID int 来访者ID
VTime datetime 来访时间有这些例子数据
1 12 2011-1-4 12:08:00
2 13 2011-1-5 09:05:00
5 18 2011-2-5 11:06:00
6 12 2011-2-6 11:08:00
7 12 2011-2-6 16:06:00
7 5 2011-3-7 16:26:00
8 19 2011-4-8 19:28:00
9 19 2011-4-9 09:28:00
...若希望查出最近的前两位访问者ID,该如何做呢?
答案
-
use Tempdb go --> --> if not object_id(N'Tempdb..#1') is null drop table #1 Go Create table #1([ID] int,[VID] int,[VTime] Datetime) Insert #1 select 1,12,'2011-1-4 12:08:00' union all select 2,13,'2011-1-5 09:05:00' union all select 5,18,'2011-2-5 11:06:00' union all select 6,12,'2011-2-6 11:08:00' union all select 7,12,'2011-2-6 16:06:00' union all select 7,5,'2011-3-7 16:26:00' union all select 8,19,'2011-4-8 19:28:00' union all select 9,19,'2011-4-9 09:28:00' Go --TOP 2 前2名訪問者 Select TOP 2* from #1 AS a WHERE NOT EXISTS(SELECT 1 FROM #1 WHERE VID=a.VID AND [VTime]>a.[VTime]) ORDER BY a.VTime DESC
ROY WU(吳熹)- 已标记为答案 Jian2011 2011年4月11日 5:41
全部回复
-
use Tempdb go --> --> if not object_id(N'Tempdb..#1') is null drop table #1 Go Create table #1([ID] int,[VID] int,[VTime] Datetime) Insert #1 select 1,12,'2011-1-4 12:08:00' union all select 2,13,'2011-1-5 09:05:00' union all select 5,18,'2011-2-5 11:06:00' union all select 6,12,'2011-2-6 11:08:00' union all select 7,12,'2011-2-6 16:06:00' union all select 7,5,'2011-3-7 16:26:00' union all select 8,19,'2011-4-8 19:28:00' union all select 9,19,'2011-4-9 09:28:00' Go --TOP 2 前2名訪問者 Select TOP 2* from #1 AS a WHERE NOT EXISTS(SELECT 1 FROM #1 WHERE VID=a.VID AND [VTime]>a.[VTime]) ORDER BY a.VTime DESC
ROY WU(吳熹)- 已标记为答案 Jian2011 2011年4月11日 5:41