表TT中有两个字段name,money,表中存有n条记录,但其中name相同,money不同,现要求取表中前10条记录,其中这10条记录中name如果相同,就取其中的第一条,如此类推,取10条。
例:
4AB6 4.120000
4AB6 3.950000
4AB6 4.160000
4AD5 6.930000
4AD5 6.830000
4AD5 6.610000
4AD5 8.840000
4AC4 49.570000
4AC4 48.970000
4AJ2 253.790000
4AJ2 240.770000结果:
4AB6 4.120000
4AD5 6.930000
4AC4 49.570000
4AJ2 253.790000
... ...
... ...
... ...5AM2 3.690000总共10条
例:
4AB6 4.120000
4AB6 3.950000
4AB6 4.160000
4AD5 6.930000
4AD5 6.830000
4AD5 6.610000
4AD5 8.840000
4AC4 49.570000
4AC4 48.970000
4AJ2 253.790000
4AJ2 240.770000结果:
4AB6 4.120000
4AD5 6.930000
4AC4 49.570000
4AJ2 253.790000
... ...
... ...
... ...5AM2 3.690000总共10条
(select distinct name,first_value(money) over(partition by name order by rownum) money from tt)
where rownum < 11;
SELECT * FROM (SELECT * FROM TT T1 WHERE T1.ROWID = (SELECT MIN(T2.ROWID) FROM TT T2 WHERE T1.NAME = T2.NAME)) WHRERE rownum <= 10;
FROM
(
SELECT NAME,MONEY,ROW_NUMBER()OVER(PARTITION BY NAME) ROW_NUM
FROM T
)T2
WHERE T2.ROW_NUM = 1
AND ROWNUM <=10
不过这样不走索引,效率比较低但是能实现
SELECT name,money FROM (
SELECT name,money,SUM(1) over (PARTITION BY name ORDER BY ROWNUM) cid
FROM TT
) WHERE cid=1 AND Rownum<11
select name,money from(
select name,money,row_number()over(partition by name order by money desc) id
from tt
)
where id =1 and rownum < 11;
--order by 语句是必要的,可以是升序(asc),看你需要了。
--如果是asc的话,当name相同的时候出来的是 money 最小的。