双11狂欢大促

盲盒赢 定制羽绒服

开启惊喜盲盒，抽取你的专属好礼

— 惊喜奖品抢先看 —

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

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

2013-07-26 10:41:42 423 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

lanneret1234 墨衍会员 · AI 创作全网分发关注 + 加入社区

【Arduino 101】五分钟搞懂PID控制算法【Arduino 101】五分钟搞懂PID控制算法物料清单尺寸接线文章目录物料清单尺寸接线物料清单 Arduino Uno x 1 超声波模块（HC-SR04）x 1 舵机（Tower Pro MG996R）x 1 舵机供电： 5V 稳压管(LM7805) x 1 , 滤波电容 (0.33uF, 0.1uF) x 2， 9V 电池 x 1 滑轨：泡沫板支架：乐高滚动物体：胶带（Up试过网球，但是测距模块的表现不理想）尺寸接线 ... 阅读详情

表
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

求搭救

...全文

UG\NX二次开发获取对象的层、颜色、隐藏状态、线宽、线型、高亮显示状态获取对象的层、颜色、隐藏状态、线宽、线型、高亮显示状态。阅读详情

lanneret1234

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

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
*/

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实在不太懂

✦双11狂欢大促✦

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

相关推荐

双11狂欢大促