oracle WHERE子句中的每条记录的表中只有3行

zrfyljdw 于 2023-04-05 发布在 Oracle

关注(0)|答案(2)|浏览(190)

我有一个大表，我必须找到3个唯一的id记录，从每个状态，我在子句中。尼斯时，如果发现3记录，他停止寻找这个状态，并会寻找下一个最多3。表是非常大的，我关心的效率和搜索将被使用多次。
我有：
| ID|地位|
| --------------|--------------|
| 1|a|
| 1|B|
| 1|B|
| 1|d|
| 第二章|d|
| 第二章|B|
| 第二章|B|
| 第二章|c|
| 第二章|B|
| 三|a|
| 三|a|
| 三|B|
| 三|e|
| 四|a|
| 四|B|
| 五|a|
| 五|B|

SELECT a.* 
FROM (
  select
    id,
    status,
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY status) as rn,
    ROW_NUMBER() OVER(PARTITION BY status ORDER BY id) as rne
  from x 
  where status in ('a', 'b' , 'e') 
) a 
WHERE rn <= 1 and rne <=3;

这段代码查找记录的时间太长了，我需要有更高效的东西
我需要的结果：
| 身份证|地位|
| --------------|--------------|
| 1|a|
| 三|a|
| 四|a|
| 1|B|
| 第二章|B|
| 三|B|
| 三|e|

oracle

来源：https://stackoverflow.com/questions/75931757/only-3-rows-from-the-table-from-each-record-in-where-clause

2条答案

按热度按时间

k0pti3hp1#

试试这个（你需要一个列来排序，这里我添加了“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
;

赞(0）回复(0）举报 2023-04-05

oprakyz72#

尝试组合使用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

赞(0）回复(0）举报 2023-04-05

我来回答

oracle WHERE子句中的每条记录的表中只有3行

2条答案

相关问题

热门标签

最新问答