select * from (select distinct name,age,time from a order by time desc ) where rownum<11;
select * from (select distinct name from a order by time desc ) where rownum<11; 不能实现name值重复取其中一个的功能 distinct 是针对一条记录相同,而不是一个字段相同 还有高人吗 帮忙解答一下
WITH A AS( SELECT TO_DATE('20091231','YYYYMMDD') TIME, 'NAME0' NAME, 16 AGE FROM DUAL UNION ALL SELECT TO_DATE('20100101','YYYYMMDD') TIME, 'NAME1' NAME, 17 AGE FROM DUAL UNION ALL SELECT TO_DATE('20100102','YYYYMMDD') TIME, 'NAME2' NAME, 18 AGE FROM DUAL UNION ALL SELECT TO_DATE('20100103','YYYYMMDD') TIME, 'NAME3' NAME, 19 AGE FROM DUAL UNION ALL SELECT TO_DATE('20100104','YYYYMMDD') TIME, 'NAME4' NAME, 20 AGE FROM DUAL UNION ALL SELECT TO_DATE('20100105','YYYYMMDD') TIME, 'NAME5' NAME, 21 AGE FROM DUAL UNION ALL SELECT TO_DATE('20100106','YYYYMMDD') TIME, 'NAME6' NAME, 22 AGE FROM DUAL UNION ALL SELECT TO_DATE('20100107','YYYYMMDD') TIME, 'NAME7' NAME, 23 AGE FROM DUAL UNION ALL SELECT TO_DATE('20100108','YYYYMMDD') TIME, 'NAME8' NAME, 24 AGE FROM DUAL UNION ALL SELECT TO_DATE('20100109','YYYYMMDD') TIME, 'NAME9' NAME, 25 AGE FROM DUAL UNION ALL SELECT TO_DATE('20100110','YYYYMMDD') TIME, 'NAME10' NAME, 50 AGE FROM DUAL UNION ALL SELECT TO_DATE('20100111','YYYYMMDD') TIME, 'NAME10' NAME, 27 AGE FROM DUAL )SELECT ITEM,NAME,AGE FROM ( SELECT MAX(TIME) ITEM,NAME ,MAX(AGE) KEEP(DENSE_RANK FIRST ORDER BY TIME DESC) AGE ,RANK() OVER(ORDER BY MAX(TIME) DESC) AA FROM A GROUP BY NAME ) B WHERE AA < 11
select name,age,time from (select name,age,time from (select name,age,time, row_number() over(partition by name order by time desc) rn from a) where rn=1) where rownum<=10
WITH A AS( SELECT TO_DATE('20091231','YYYYMMDD') TIME, 'NAME0' NAME, 16 AGE FROM DUAL UNION ALL SELECT TO_DATE('20100101','YYYYMMDD') TIME, 'NAME1' NAME, 17 AGE FROM DUAL UNION ALL SELECT TO_DATE('20100102','YYYYMMDD') TIME, 'NAME2' NAME, 18 AGE FROM DUAL UNION ALL SELECT TO_DATE('20100103','YYYYMMDD') TIME, 'NAME3' NAME, 19 AGE FROM DUAL UNION ALL SELECT TO_DATE('20100104','YYYYMMDD') TIME, 'NAME4' NAME, 20 AGE FROM DUAL UNION ALL SELECT TO_DATE('20100105','YYYYMMDD') TIME, 'NAME5' NAME, 21 AGE FROM DUAL UNION ALL SELECT TO_DATE('20100106','YYYYMMDD') TIME, 'NAME6' NAME, 22 AGE FROM DUAL UNION ALL SELECT TO_DATE('20100107','YYYYMMDD') TIME, 'NAME7' NAME, 23 AGE FROM DUAL UNION ALL SELECT TO_DATE('20100108','YYYYMMDD') TIME, 'NAME8' NAME, 24 AGE FROM DUAL UNION ALL SELECT TO_DATE('20100109','YYYYMMDD') TIME, 'NAME9' NAME, 25 AGE FROM DUAL UNION ALL SELECT TO_DATE('20100110','YYYYMMDD') TIME, 'NAME10' NAME, 26 AGE FROM DUAL UNION ALL SELECT TO_DATE('20100111','YYYYMMDD') TIME, 'NAME10' NAME, 27 AGE FROM DUAL )SELECT B.* FROM ( SELECT TIME,NAME,AGE FROM A WHERE NOT EXISTS(SELECT 1 FROM A A1 WHERE A1.NAME = A.NAME AND A1.TIME > A.TIME) ORDER BY TIME DESC ) B WHERE ROWNUM < 11
select name from tb a where not exists(select 1 from tb b where a.time<b.time and a.name=b.name) and rownum<=10
--测试85万多条记录,不到 1 秒:Select A1.Time, A1.Age, A3.Max_Time From a A1, (Select A2.Name, Max(A2.Time) As Max_Time From a A2 Group By A2.Name) A3 Where A1.Name = A3.Name And A1.Time = A3.Time And Rownum < 11 Order By A3.Max_Time Desc;
select name,age,time from ( select name,age,time,row_number()over(order by time desc) rn1 from (select name,age,time, row_number() over(partition by name order by time desc) rn from a) where rn=1 ) where rn1<=10;
select time,name,age from ( select a.*,rank () over(partition by name order by time desc nulls last) as rank_num from a) h where h.rank_num=1 and rownum<11
我要取包含三个字段的记录,而不是只是name字段
select * from (select distinct name,age,time from a order by time desc ) where rownum<11;
不能实现name值重复取其中一个的功能
distinct 是针对一条记录相同,而不是一个字段相同
还有高人吗
帮忙解答一下
SELECT TO_DATE('20091231','YYYYMMDD') TIME, 'NAME0' NAME, 16 AGE FROM DUAL
UNION ALL
SELECT TO_DATE('20100101','YYYYMMDD') TIME, 'NAME1' NAME, 17 AGE FROM DUAL
UNION ALL
SELECT TO_DATE('20100102','YYYYMMDD') TIME, 'NAME2' NAME, 18 AGE FROM DUAL
UNION ALL
SELECT TO_DATE('20100103','YYYYMMDD') TIME, 'NAME3' NAME, 19 AGE FROM DUAL
UNION ALL
SELECT TO_DATE('20100104','YYYYMMDD') TIME, 'NAME4' NAME, 20 AGE FROM DUAL
UNION ALL
SELECT TO_DATE('20100105','YYYYMMDD') TIME, 'NAME5' NAME, 21 AGE FROM DUAL
UNION ALL
SELECT TO_DATE('20100106','YYYYMMDD') TIME, 'NAME6' NAME, 22 AGE FROM DUAL
UNION ALL
SELECT TO_DATE('20100107','YYYYMMDD') TIME, 'NAME7' NAME, 23 AGE FROM DUAL
UNION ALL
SELECT TO_DATE('20100108','YYYYMMDD') TIME, 'NAME8' NAME, 24 AGE FROM DUAL
UNION ALL
SELECT TO_DATE('20100109','YYYYMMDD') TIME, 'NAME9' NAME, 25 AGE FROM DUAL
UNION ALL
SELECT TO_DATE('20100110','YYYYMMDD') TIME, 'NAME10' NAME, 50 AGE FROM DUAL
UNION ALL
SELECT TO_DATE('20100111','YYYYMMDD') TIME, 'NAME10' NAME, 27 AGE FROM DUAL
)SELECT ITEM,NAME,AGE
FROM (
SELECT MAX(TIME) ITEM,NAME
,MAX(AGE) KEEP(DENSE_RANK FIRST ORDER BY TIME DESC) AGE
,RANK() OVER(ORDER BY MAX(TIME) DESC) AA
FROM A
GROUP BY NAME
) B WHERE AA < 11
select name,age,time
from (select name,age,time
from (select name,age,time,
row_number() over(partition by name order by time desc) rn from a) where rn=1)
where rownum<=10
WITH A AS(
SELECT TO_DATE('20091231','YYYYMMDD') TIME, 'NAME0' NAME, 16 AGE FROM DUAL
UNION ALL
SELECT TO_DATE('20100101','YYYYMMDD') TIME, 'NAME1' NAME, 17 AGE FROM DUAL
UNION ALL
SELECT TO_DATE('20100102','YYYYMMDD') TIME, 'NAME2' NAME, 18 AGE FROM DUAL
UNION ALL
SELECT TO_DATE('20100103','YYYYMMDD') TIME, 'NAME3' NAME, 19 AGE FROM DUAL
UNION ALL
SELECT TO_DATE('20100104','YYYYMMDD') TIME, 'NAME4' NAME, 20 AGE FROM DUAL
UNION ALL
SELECT TO_DATE('20100105','YYYYMMDD') TIME, 'NAME5' NAME, 21 AGE FROM DUAL
UNION ALL
SELECT TO_DATE('20100106','YYYYMMDD') TIME, 'NAME6' NAME, 22 AGE FROM DUAL
UNION ALL
SELECT TO_DATE('20100107','YYYYMMDD') TIME, 'NAME7' NAME, 23 AGE FROM DUAL
UNION ALL
SELECT TO_DATE('20100108','YYYYMMDD') TIME, 'NAME8' NAME, 24 AGE FROM DUAL
UNION ALL
SELECT TO_DATE('20100109','YYYYMMDD') TIME, 'NAME9' NAME, 25 AGE FROM DUAL
UNION ALL
SELECT TO_DATE('20100110','YYYYMMDD') TIME, 'NAME10' NAME, 26 AGE FROM DUAL
UNION ALL
SELECT TO_DATE('20100111','YYYYMMDD') TIME, 'NAME10' NAME, 27 AGE FROM DUAL
)SELECT B.*
FROM (
SELECT TIME,NAME,AGE
FROM A
WHERE NOT EXISTS(SELECT 1 FROM A A1 WHERE A1.NAME = A.NAME AND A1.TIME > A.TIME)
ORDER BY TIME DESC
) B WHERE ROWNUM < 11
from tb a where not exists(select 1 from tb b where a.time<b.time and a.name=b.name)
and rownum<=10
From a A1,
(Select A2.Name, Max(A2.Time) As Max_Time From a A2 Group By A2.Name) A3
Where A1.Name = A3.Name
And A1.Time = A3.Time
And Rownum < 11
Order By A3.Max_Time Desc;
from (
select name,age,time,row_number()over(order by time desc) rn1
from (select name,age,time,
row_number() over(partition by name order by time desc) rn from a) where rn=1 )
where rn1<=10;
最外层用rownum<11
time,name,age
from
(
select a.*,rank () over(partition by name order by time desc nulls last) as rank_num
from a) h
where h.rank_num=1 and rownum<11