;with cte as (select row_number()over(partition by User order by dt desc) as N,* from table ) select * from cte where N=1
select * from tbname t where not exists (select 1 from tbname where userName = t.userName and shijian > t.shijian)
--如果你的表有自增id什么的select * from tbname t where id = (select top 1 id from tbname where userName = t.userName order by shijian desc)
表名:UserWork, 自增ID,用户名:UserName,用户操作:UserWork,时间字段:WorkTime 例如 1 a1 eat food 2011/07/26 2 a1 run 2011/07/27 3 a2 run 2011/07/27 4 a3 eat food 2011/07/24 5 a3 run 2011/07/18 该如何查出a1,a2,a3最近操作的一条记录
select * from UserWork a where not exists (select 1 from UserWork b where a.UserName=b.UserName and b.v>a.WorkTime)
--上面错了,看这里 select * from UserWork a where not exists (select 1 from UserWork b where a.UserName=b.UserName and b.WorkTime>a.WorkTime)
USE master; GO SET NOCOUNT ON IF OBJECT_ID(N'A',N'U')IS NOT NULL DROP TABLE A GOCREATE TABLE A--创建测试数据表 ( id INT IDENTITY(1,1) NOT NULL PRIMARY KEY , UserName nvarchar(10), UserWork nvarchar(10), WorkTime date ) INSERT INTO A--插入测试数据 SELECT 'a1', 'eat food', '2011/07/26' UNION ALL SELECT 'a1', 'run','2011/07/27' UNION ALL SELECT 'a2', 'run', '2011/07/27' UNION ALL SELECT 'a3', 'eatfood','2011/07/24' UNION ALL SELECT 'a3', 'run', '2011/07/18' goSELECT id, UserName,UserWork,WorkTime FROM (select *,ROW_NUMBER()OVER(PARTITION BY UserName ORDER BY WorkTime DESC) AS RN from A)AS B WHERE RN=1 ORDER BY id/* id UserName UserWork WorkTime ----------- ---------- ---------- ---------- 2 a1 run 2011-07-27 3 a2 run 2011-07-27 4 a3 eatfood 2011-07-24*/go IF OBJECT_ID(N'A',N'U')IS NOT NULL DROP TABLE A GO
(select row_number()over(partition by User order by dt desc) as N,* from table )
select *
from cte
where N=1
select *
from tbname t
where not exists (select 1 from tbname where userName = t.userName and shijian > t.shijian)
--如果你的表有自增id什么的select *
from tbname t
where id = (select top 1 id from tbname where userName = t.userName order by shijian desc)
自增ID,用户名:UserName,用户操作:UserWork,时间字段:WorkTime
例如
1 a1 eat food 2011/07/26
2 a1 run 2011/07/27
3 a2 run 2011/07/27
4 a3 eat food 2011/07/24
5 a3 run 2011/07/18
该如何查出a1,a2,a3最近操作的一条记录
select * from UserWork a
where not exists
(select 1 from UserWork b where a.UserName=b.UserName and b.v>a.WorkTime)
select * from UserWork a
where not exists
(select 1 from UserWork b where a.UserName=b.UserName and b.WorkTime>a.WorkTime)
GO
SET NOCOUNT ON
IF OBJECT_ID(N'A',N'U')IS NOT NULL DROP TABLE A
GOCREATE TABLE A--创建测试数据表
(
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY ,
UserName nvarchar(10),
UserWork nvarchar(10),
WorkTime date
)
INSERT INTO A--插入测试数据
SELECT 'a1', 'eat food', '2011/07/26' UNION ALL
SELECT 'a1', 'run','2011/07/27' UNION ALL
SELECT 'a2', 'run', '2011/07/27' UNION ALL
SELECT 'a3', 'eatfood','2011/07/24' UNION ALL
SELECT 'a3', 'run', '2011/07/18'
goSELECT id, UserName,UserWork,WorkTime FROM
(select *,ROW_NUMBER()OVER(PARTITION BY UserName ORDER BY WorkTime DESC) AS RN from A)AS B
WHERE RN=1 ORDER BY id/*
id UserName UserWork WorkTime
----------- ---------- ---------- ----------
2 a1 run 2011-07-27
3 a2 run 2011-07-27
4 a3 eatfood 2011-07-24*/go
IF OBJECT_ID(N'A',N'U')IS NOT NULL DROP TABLE A
GO