有一个数据表,是读取记录
id userID pysicID time
1 1 10 2011-07-20 22:42:50.343
2 1 10 2011-07-20 23:12:25.172
3 1 11 2011-07-21 12:12:24.232
4 1 10 2011-07-21 14:32:25.125我想取出每个pysicID最近的读取记录
就像id userID pysicID time
2 1 10 2011-07-20 23:12:25.172
3 1 11 2011-07-21 12:12:24.232这样
这个select该咋写呢?
id userID pysicID time
1 1 10 2011-07-20 22:42:50.343
2 1 10 2011-07-20 23:12:25.172
3 1 11 2011-07-21 12:12:24.232
4 1 10 2011-07-21 14:32:25.125我想取出每个pysicID最近的读取记录
就像id userID pysicID time
2 1 10 2011-07-20 23:12:25.172
3 1 11 2011-07-21 12:12:24.232这样
这个select该咋写呢?
with cte as
(
select * ,row_number()over(partition by pysicID order by time desc) as RN
from tb
)
select * from cte where RN=1
*
from
tb t
where
time =(select max(time) from tb where pysicID=t. pysicID)
where not exists
(select 1 from tb b where a.userID=b.userID and a.pysicID=b.pysicID and b.time>a.time)
where not exists
(select 1 from tb b where a.userID=b.userID and a.pysicID=b.pysicID and b.time>a.time)
2 1 10 2011-07-20 23:12:25.172
4 1 10 2011-07-21 14:32:25.125应该是这个结果,上面写错了
SELECT * FROM TB WHERE TIME
IN
(
SELECT MAX(time) FROM TB GROUP BY pysicID
)
每个pysicID
就是id为 2和3
pysicid为 10 和 11问题提清除