select * from (select t.*,row_number()over( partition by 第2列 order by 第1列) as rn from test1 t) where rn<=3
--测试数据 CREATE TABLE T(ID1 NUMBER,ID2 NUMBER,ID3 NUMBER); INSERT INTO T VALUES(1,1,1); INSERT INTO T VALUES(2,1,2); INSERT INTO T VALUES(3,1,3); INSERT INTO T VALUES(4,1,4); INSERT INTO T VALUES(5,2,5); INSERT INTO T VALUES(6,2,6); INSERT INTO T VALUES(7,2,6); INSERT INTO T VALUES(8,2,6); commit;--语句 SELECT ID1,ID2,ID3 FROM (SELECT T.*,ROW_NUMBER() OVER (PARTITION BY ID2 ORDER BY ID1) RN FROM T) WHERE RN<=3;--结果 ID1 ID2 ID3 1 1 1 2 1 2 3 1 3 5 2 5 6 2 6 7 2 6
楼上已经是正确答案了。引用一下。 SQL codeselect * from (select t.*,row_number()over( partition by 第2列 order by 第1列) as rn from test1 t) where rn <=3 还是好好看看oracle的分析函数吧!
from test1 t) where rn<=3
CREATE TABLE T(ID1 NUMBER,ID2 NUMBER,ID3 NUMBER);
INSERT INTO T VALUES(1,1,1);
INSERT INTO T VALUES(2,1,2);
INSERT INTO T VALUES(3,1,3);
INSERT INTO T VALUES(4,1,4);
INSERT INTO T VALUES(5,2,5);
INSERT INTO T VALUES(6,2,6);
INSERT INTO T VALUES(7,2,6);
INSERT INTO T VALUES(8,2,6);
commit;--语句
SELECT ID1,ID2,ID3 FROM
(SELECT T.*,ROW_NUMBER() OVER (PARTITION BY ID2 ORDER BY ID1) RN FROM T)
WHERE RN<=3;--结果
ID1 ID2 ID3
1 1 1
2 1 2
3 1 3
5 2 5
6 2 6
7 2 6
SQL codeselect * from (select t.*,row_number()over( partition by 第2列 order by 第1列) as rn
from test1 t) where rn <=3
还是好好看看oracle的分析函数吧!