各位朋友,表LOCK结构如下:
LOCKID varchar(8),
ChangeTime varchar(20)现在我要查出该表中所有LOCKID的ChangeTime为第二大的所有记录
如原数据
LOCKID ChangeTime
00000001 2005-06-16 09:00:00
00000001 2005-06-16 10:00:00
00000001 2005-06-16 11:00:00
00000002 2005-06-15 09:00:00
00000002 2005-06-15 10:00:00
00000002 2005-06-15 11:00:00选择结果如下
00000001 2005-06-16 10:00:00
00000002 2005-06-15 10:00:00推而广之,一条SQL语句能不选出所有LOCK第n大的记录!!!
LOCKID varchar(8),
ChangeTime varchar(20)现在我要查出该表中所有LOCKID的ChangeTime为第二大的所有记录
如原数据
LOCKID ChangeTime
00000001 2005-06-16 09:00:00
00000001 2005-06-16 10:00:00
00000001 2005-06-16 11:00:00
00000002 2005-06-15 09:00:00
00000002 2005-06-15 10:00:00
00000002 2005-06-15 11:00:00选择结果如下
00000001 2005-06-16 10:00:00
00000002 2005-06-15 10:00:00推而广之,一条SQL语句能不选出所有LOCK第n大的记录!!!
select LOCKID,ChangeTime from (select LOCK.*,dense_rank() over(partition by LOCKID order by ChangeTime desc) rk from LOCK) where rk = n
(select LOCKID,ChangeTime,rank() over(partition by lockid order by ChangeTime desc) rk
from lock) where rk=2;
这种情况也要考虑啊?在这里不用吧,
不知道能不能用MINUS 来解决...