查询每个人最晚的纪录!!求搭救

lanneret1234 2013-07-26 10:41:42

index userid workid time
1 1 1 2013-07-23 12:38:10
2 2 2 2013-07-23 12:38:11
3 3 3 2013-07-23 12:38:12
4 1 4 2013-07-23 12:38:13
5 2 5 2013-07-23 12:38:14
6 3 6 2013-07-23 12:38:15

想要查出每个人最晚那条记录 如
4 1 4 2013-07-23 12:38:13
5 2 5 2013-07-23 12:38:14
6 3 6 2013-07-23 12:38:15

求搭救
...全文
367 16 打赏 收藏 转发到动态 举报
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
Joel_L 2013-08-03
  • 打赏
  • 举报
回复
引用 3 楼 hdhai9451 的回复:
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
*/
ok
E次奥 2013-08-02
  • 打赏
  • 举报
回复
12不正确!
Shawn 2013-08-02
  • 打赏
  • 举报
回复
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
*/
rubynle 2013-08-02
  • 打赏
  • 举报
回复
select * from (select *,row_number() over(partition by userid order by time desc) as RowNO from table1) as a where a.RowNO=1
hgwyl 2013-07-26
  • 打赏
  • 举报
回复
二次循环 第一循环:人员 ——可以用user表 ——也可以group by一下 第二循环:top 1 where 姓名=第一循环的姓名 order by time desc
---涛声依旧--- 2013-07-26
  • 打赏
  • 举报
回复
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' WITH CTE AS (SELECT ROW_NUMBER() OVER(PARTITION BY userid ORDER BY [time] DESC) AS [rank],* FROM tb ) SELECT * FROM CTE WHERE [rank] =1 drop table tb /* rank index userid workid time 1 4 1 4 2013-07-23 12:38:13.000 1 5 2 5 2013-07-23 12:38:14.000 1 6 3 6 2013-07-23 12:38:15.000 */
黄_瓜 2013-07-26
  • 打赏
  • 举报
回复
--> 生成测试数据: #T IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T CREATE TABLE #T (ID VARCHAR(3),GID INT,Author VARCHAR(29),Title VARCHAR(39),Date DATETIME) INSERT INTO #T SELECT '001',1,'邹建','深入浅出SQLServer2005开发管理与应用实例','2008-05-10' UNION ALL SELECT '002',1,'胡百敬','SQLServer2005性能调校','2008-03-22' UNION ALL SELECT '003',1,'格罗夫Groff.J.R.','SQL完全手册','2009-07-01' UNION ALL SELECT '004',1,'KalenDelaney','SQLServer2005技术内幕存储引擎','2008-08-01' UNION ALL SELECT '005',2,'Alex.Kriegel.Boris.M.Trukhnov','SQL宝典','2007-10-05' UNION ALL SELECT '006',2,'飞思科技产品研发中心','SQLServer2000高级管理与开发','2007-09-10' UNION ALL SELECT '007',2,'胡百敬','SQLServer2005数据库开发详解','2008-06-15' UNION ALL SELECT '008',3,'陈浩奎','SQLServer2000存储过程与XML编程','2005-09-01' UNION ALL SELECT '009',3,'赵松涛','SQLServer2005系统管理实录','2008-10-01' UNION ALL SELECT '010',3,'黄占涛','SQL技术手册','2006-01-01' --SQL查询如下: --按GID分组,查每个分组中Date最新的前2条记录 --1.字段ID唯一时: SELECT * FROM #T AS T WHERE ID IN(SELECT TOP 2 ID FROM #T WHERE GID=T.GID ORDER BY Date DESC) --2.如果ID不唯一时: SELECT * FROM #T AS T WHERE 2>(SELECT COUNT(*) FROM #T WHERE GID=T.GID AND Date>T.Date) --SQL Server 2005 使用新方法 --3.使用ROW_NUMBER()进行排位分组 SELECT ID,GID,Author,Title,Date FROM ( SELECT rid=ROW_NUMBER() OVER(PARTITION BY GID ORDER BY Date DESC),* FROM #T ) AS T WHERE rid<=2 --4.使用APPLY SELECT DISTINCT b.* FROM #T AS a CROSS APPLY ( SELECT TOP(2) * FROM #T WHERE a.GID=GID ORDER BY Date DESC ) AS b --结果 /* ID GID Author Title Date ---- ----------- ----------------------------- --------------------------------------- ----------------------- 003 1 格罗夫Groff.J.R. SQL完全手册 2009-07-01 00:00:00.000 004 1 KalenDelaney SQLServer2005技术内幕存储引擎 2008-08-01 00:00:00.000 005 2 Alex.Kriegel.Boris.M.Trukhnov SQL宝典 2007-10-05 00:00:00.000 007 2 胡百敬 SQLServer2005数据库开发详解 2008-06-15 00:00:00.000 009 3 赵松涛 SQLServer2005系统管理实录 2008-10-01 00:00:00.000 010 3 黄占涛 SQL技术手册 2006-01-01 00:00:00.000 (6 行受影响) */
Andy__Huang 2013-07-26
  • 打赏
  • 举报
回复
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
*/
Andy__Huang 2013-07-26
  • 打赏
  • 举报
回复
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
哥眼神纯洁不 2013-07-26
  • 打赏
  • 举报
回复
select * from 表 a where time=(select max(time) from 表 where a.userid=userid)
Tosp2012 2013-07-26
  • 打赏
  • 举报
回复
引用 楼主 lanneret1234 的回复:
表 index userid workid time 1 1 1 2013-07-23 12:38:10 2 2 2 2013-07-23 12:38:11 3 3 3 2013-07-23 12:38:12 4 1 4 2013-07-23 12:38:13 5 2 5 2013-07-23 12:38:14 6 3 6 2013-07-23 12:38:15 想要查出每个人最晚那条记录 如 4 1 4 2013-07-23 12:38:13 5 2 5 2013-07-23 12:38:14 6 3 6 2013-07-23 12:38:15 求搭救


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] 
              )
唐诗三百首 2013-07-26
  • 打赏
  • 举报
回复
try this,

-- 建索引
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
雪狐 2013-07-26
  • 打赏
  • 举报
回复
2005 Row_Number + Over子句
Mr_Nice 2013-07-26
  • 打赏
  • 举报
回复
引用 8 楼 lanneret1234 的回复:
是50万条数据
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*/
lanneret1234 2013-07-26
  • 打赏
  • 举报
回复
是50万条数据
lanneret1234 2013-07-26
  • 打赏
  • 举报
回复
50条数据 查了35分钟 是语句效率差 还是我电脑差 rockyljt 执行不了 hgwyl 能提供个例子吗,对sql实在不太懂

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧