表有两个字段 username 和 addtime
现在每天想取前三位用户 怎么写sql语句?查询结果类似如下:
abc 2010-12-20
abc 2010-12-20
abc 2010-12-20abc 2010-12-21
abc 2010-12-21
abc 2010-12-21abc 2010-12-22
abc 2010-12-22
abc 2010-12-22abc 2010-12-23
abc 2010-12-23
abc 2010-12-23
现在每天想取前三位用户 怎么写sql语句?查询结果类似如下:
abc 2010-12-20
abc 2010-12-20
abc 2010-12-20abc 2010-12-21
abc 2010-12-21
abc 2010-12-21abc 2010-12-22
abc 2010-12-22
abc 2010-12-22abc 2010-12-23
abc 2010-12-23
abc 2010-12-23
from tb t
where (select count(1) from tb where username=t.username and addtime<=t.addtime)<=3
select *
from tb t
where (select count(1) from tb where username=t.username and datediff(dd,addtime,t.addtime)=0 and addtime<=t.addtime)<=3
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([username] varchar(3),[addtime] datetime)
insert [TB]
select 'abc','2010-12-20' union all
select 'abc','2010-12-20' union all
select 'abc','2010-12-20' union all
select 'abc','2010-12-20' union all
select 'abc','2010-12-21' union all
select 'abc','2010-12-21' union all
select 'abc','2010-12-21' union all
select 'abc','2010-12-22' union all
select 'abc','2010-12-22' union all
select 'abc','2010-12-22' union all
select 'abc','2010-12-23' union all
select 'abc','2010-12-23' union all
select 'abc','2010-12-23'
GO--> 查询结果
;with a as (
SELECT *,ROW_NUMBER()over(partition by [username],[addtime] order by [username],[addtime]) as px FROM [TB])
select * from a where px<=3--> 删除表格
--DROP TABLE [TB]
好像也不太对 你也username 改成不一样试试看
SELECT *,ROW_NUMBER()over(partition by [addtime] order by [username],[addtime]) as px FROM [TB])
select * from a where px<=3去掉username就可以了
select * from #tb a where id in (select top 3.id from #tb where date = a.date)没编译基本思路吧
借用此测试数据...select identity(int,1,1) as cid,* into #t from tb
select * from #t t
where 3>(select count(1) from #t where addtime=t.addtime and cid>t.cid)
order by addtime,cid desc/*
4 abc 2010-12-20 00:00:00.000
3 abc 2010-12-20 00:00:00.000
2 abc 2010-12-20 00:00:00.000
7 abc 2010-12-21 00:00:00.000
6 abc 2010-12-21 00:00:00.000
5 abc 2010-12-21 00:00:00.000
10 abc 2010-12-22 00:00:00.000
9 abc 2010-12-22 00:00:00.000
8 abc 2010-12-22 00:00:00.000
13 abc 2010-12-23 00:00:00.000
12 abc 2010-12-23 00:00:00.000
11 abc 2010-12-23 00:00:00.000
*/