where子句中的每个记录只从表中取出3行

Question

我有一个大表，我必须从每个状态找到3唯一的id记录，我在子句中。很好，当它找到3条记录时，它停止寻找这个状态，并将寻找下一个到3的记录。表非常大，我很关心效率，而且搜索会用到很多次。

我有:

<表类> ID 状态 tbody><<tr>11b1b1d2d2b2b2c2b333b3e44b55b

试试这个(您需要一个列来排序，这里我添加了"rn"但也可以是日期):

with data(rn,id,status) as (
select 1, 1, 'a' from dual union all 
select 2, 1, 'b' from dual union all 
select 3, 1, 'b' from dual union all 
select 4, 1, 'd' from dual union all 
select 5, 2, 'd' from dual union all 
select 6, 2, 'b' from dual union all 
select 7, 2, 'b' from dual union all 
select 8, 2, 'c' from dual union all 
select 9, 2, 'b' from dual union all 
select 10, 3, 'a' from dual union all 
select 11, 3, 'a' from dual union all 
select 12, 3, 'b' from dual union all 
select 13, 3, 'e' from dual union all 
select 14, 4, 'a' from dual union all 
select 15, 4, 'b' from dual union all 
select 16, 5, 'a' from dual union all 
select 17, 5, 'b' from dual 
)
select id, status
from (
select id, status,
dense_rank() over(partition by status order by rn) as rnk
from (
select 
rn,
dense_rank() over(partition by status, id order by rn) as rnk1,
id, status
from (
select rn, id, status
from (
select rn, id, status
, lag(status) over(order by rn) as prev_status
from data
)
where status <> prev_status or prev_status is null
)
)
where rnk1 = 1
)
where rnk <= 3
;

尝试组合Group By以消除重复，并使用简单的Count() Over()分析函数以每个STATUS仅获得三行。

WITH  
tbl (ID, STATUS) AS 
(
Select  1,  'a' From Dual Union All 
Select  1,  'b' From Dual Union All 
Select  1,  'b' From Dual Union All 
Select  1,  'd' From Dual Union All 
Select  2,  'd' From Dual Union All 
Select  2,  'b' From Dual Union All 
Select  2,  'b' From Dual Union All 
Select  2,  'c' From Dual Union All 
Select  2,  'b' From Dual Union All 
Select  3,  'a' From Dual Union All 
Select  3,  'a' From Dual Union All 
Select  3,  'b' From Dual Union All 
Select  3,  'e' From Dual Union All 
Select  4,  'a' From Dual Union All 
Select  4,  'b' From Dual Union All 
Select  5,  'a' From Dual Union All 
Select  5,  'b' From Dual 
)
Select ID, STATUS
From
( Select  t.STATUS, t.ID, Count(t.ID) OVER(PARTITION BY STATUS ORDER BY ID) "RN" 
From tbl t
Group By STATUS, ID
Order By STATUS, ID 
)
Where RN <= 3 And STATUS IN('a', 'b', 'e')
ID STATUS
---------- ------
1 a
3 a
4 a
1 b
2 b
3 b
3 e