27,579
社区成员
发帖
与我相关
我的任务
分享
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] ([index] int,userid int,workid int,time datetime)
insert into [TB]
select 1,1,1,'2013-07-23 12:38:10' union all
select 2,2,2,'2013-07-23 12:38:11' union all
select 3,3,3,'2013-07-23 12:38:12' union all
select 4,1,4,'2013-07-23 12:38:13' union all
select 5,2,5,'2013-07-23 12:38:14' union all
select 6,3,6,'2013-07-23 12:38:15'
--应该创建userid asc,time desc 复合索引
CREATE INDEX IX_TB_fieldlist ON dbo.[TB]
(
userid,
[time] DESC
)
--50W数据,一般的服务器级别的硬件问题不大
SELECT b.*
FROM(SELECT DISTINCT userid FROM TB) A --不过你应该有现在的user表,用user表代替这行代码,效率会更好
CROSS APPLY
(SELECT TOP(1) * FROM TB M WHERE M.userid = A.userid ORDER BY [time] DESC) B
/*
index userid workid time
4 1 4 2013-07-23 12:38:13.000
5 2 5 2013-07-23 12:38:14.000
6 3 6 2013-07-23 12:38:15.000
*/
create table tb([index] int, userid int,workid int,[time] datetime)
insert into tb
select 1,1,1,'2013-07-23 12:38:10'
union all select 2,2,2,'2013-07-23 12:38:11'
union all select 3,3,3,'2013-07-23 12:38:12'
union all select 4,1,4,'2013-07-23 12:38:13'
union all select 5,2,5,'2013-07-23 12:38:14'
union all select 6,3,6,'2013-07-23 12:38:15'
select * from tb
select a.*
from tb a
inner join (select userid,max(time) as time from tb group by userid)b
on a.userid=b.userid and a.time=b.time
order by userid
drop table tb
/*
4 1 4 2013-07-23 12:38:13.000
5 2 5 2013-07-23 12:38:14.000
6 3 6 2013-07-23 12:38:15.000
*/
IF OBJECT_ID('Ta') IS NOT NULL
DROP TABLE Ta
GO
CREATE TABLE Ta
( [index] INT,
userid INT,
workid INT,
[time] DATETIME
)
GO
INSERT Ta
SELECT 1,1,1,'2013-07-23 12:38:10' UNION
SELECT 2,2,2,'2013-07-23 12:38:11' UNION
SELECT 3,3,3,'2013-07-23 12:38:12' UNION
SELECT 4,1,4,'2013-07-23 12:38:13' UNION
SELECT 5,2,5,'2013-07-23 12:38:14' UNION
SELECT 6,3,6,'2013-07-23 12:38:15'
SELECT *
FROM TA a
WHERE EXISTS (
SELECT 1
FROM TA b
WHERE A.userid=b.userid and a.[time]>b.[time]
)
-- 建索引
create index idx_[表名]_userid_time on [表名](userid,[time])
-- 查询
select a.*
from [表名] a
inner join
(select userid,
max([time]) 'maxtime'
from [表名]
group by userid) b on a.userid=b.userid and a.[time]=b.maxtime
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] ([index] int,userid int,workid int,time datetime)
insert into [TB]
select 1,1,1,'2013-07-23 12:38:10' union all
select 2,2,2,'2013-07-23 12:38:11' union all
select 3,3,3,'2013-07-23 12:38:12' union all
select 4,1,4,'2013-07-23 12:38:13' union all
select 5,2,5,'2013-07-23 12:38:14' union all
select 6,3,6,'2013-07-23 12:38:15'
select * from [TB]
SELECT *
FROM dbo.TB
WHERE NOT EXISTS ( SELECT 1 --注意使用not exists,最好匹配对应的索引,参考:http://blog.csdn.net/orchidcat/article/details/6267552
FROM TB B
WHERE TB.userid = B.userid
AND B.TIME > TB.TIME )
/*
index userid workid time
4 1 4 2013-07-23 12:38:13.000
5 2 5 2013-07-23 12:38:14.000
6 3 6 2013-07-23 12:38:15.000*/