Oracle查詢以查找第一行或第二行

我有一組包含人員及其條款記錄的資料。我只關心術語 201030、201040 和 201110。所有其他術語都將被忽略。有的人會有好幾條記錄，有的人只有一張。我正在撰寫一個查詢來獲取每個人的第一個術語。如果第一項是 201030，它應該查看下一行。如果是 201040 或 201110，我需要回傳第二行的術語，否則只回傳 201030。如果它是 201040 或 201110，則回傳第一行的術語。這有點令人困惑，所以我試圖用不同的場景。綠色框表示我應該保留的記錄。

這是我到目前為止的查詢。它運行但不應用案例邏輯。它只回傳第一項。我應該如何更改此查詢？

select person_id, 
       min(term) as min_term,  -- including this field to verify the 1st term before changes
       case 
          when term = '201030' 
          then lead(min(term), 1, min(term)) over (order by min(term))
          else term
       end as term
from my_table 
group by person_id

uj5u.com熱心網友回復：

從 Oracle 12 開始，您可以使用MATCH_RECOGNIZE：

SELECT *
FROM   (
  SELECT *
  FROM   my_table
  WHERE  term IN (201030, 201040, 201110)
)
MATCH_RECOGNIZE (
  PARTITION BY person_id
  ORDER     BY term_order
  ALL ROWS PER MATCH
  PATTERN ( ^ {- IS30? -} ANY_ROW )
  DEFINE
    IS30 AS term = 201030
)

在此之前，您可以使用決議函式：

SELECT person_id,
       CASE
       WHEN term = 201030
       THEN next_term
       ELSE term
       END AS term
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY term_order) AS rn,
         LEAD(term, 1, term) OVER (PARTITION BY person_id ORDER BY term_order)
           AS next_term
  FROM   my_table t
  WHERE  term IN (201030, 201040, 201110)
)
WHERE  rn = 1;

其中，對于樣本資料：

CREATE TABLE my_table (person_id, term, term_order) As
  SELECT 14627, 201030, 1 FROM DUAL UNION ALL -- Not this one
  SELECT 14627, 201110, 2 FROM DUAL UNION ALL -- This one
  SELECT 14627, 201510, 3 FROM DUAL UNION ALL -- Ignore this
  SELECT 14702, 201030, 1 FROM DUAL UNION ALL -- This one
  SELECT 28103, 201030, 1 FROM DUAL UNION ALL -- This one
  SELECT 28103, 201230, 2 FROM DUAL UNION ALL -- Ignore this
  SELECT 28103, 201240, 3 FROM DUAL UNION ALL -- Ignore this
  SELECT 28103, 201310, 4 FROM DUAL UNION ALL -- Ignore this
  SELECT 33634, 201040, 1 FROM DUAL UNION ALL -- This one
  SELECT 33634, 201110, 2 FROM DUAL UNION ALL -- Not this one
  SELECT 33634, 201130, 3 FROM DUAL UNION ALL -- Ignore this
  SELECT 32356, 201510, 1 FROM DUAL UNION ALL -- Ignore this
  SELECT 53303, 201030, 1 FROM DUAL UNION ALL -- Not this one
  SELECT 53303, 201040, 2 FROM DUAL UNION ALL -- This one.
  SELECT 53303, 201110, 3 FROM DUAL UNION ALL -- Not this one
  SELECT 53303, 201140, 4 FROM DUAL;          -- Ignore this

注意：在 SQL 中，表是無序的。如果您希望它們按特定順序排列，那么您需要有一些東西可以應用該順序（例如task_order列）并使用ORDER BY子句。

輸出：

PERSON_ID	TERM_ORDER	學期
14627	2	201110
14702	1	201030
28103	1	201030
33634	1	201040
53303	2	201040

db<>在這里擺弄

uj5u.com熱心網友回復：

這是一種選擇。閱讀代碼中的注釋。

SQL> with test (person_id, term) as
  2    -- sample data
  3    (select 53303, 201030 from dual union all
  4     select 53303, 201040 from dual union all
  5     select 53303, 201110 from dual union all
  6     select 53303, 201140 from dual union all
  7     --
  8     select 14627, 201030 from dual union all
  9     select 14627, 201110 from dual union all
 10     select 14627, 201510 from dual union all
 11     --
 12     select 14702, 201030 from dual union all
 13     --
 14     select 28103, 201030 from dual union all
 15     select 28103, 201230 from dual union all
 16     select 28103, 201240 from dual union all
 17     select 28103, 201310 from dual union all
 18     --
 19     select 33634, 201040 from dual union all
 20     select 33634, 201110 from dual union all
 21     select 33634, 201130 from dual union all
 22     --
 23     select 32356, 201510 from dual
 24    ),

 25  rns as
 26    -- select next term and row numbers
 27    (select person_id,
 28       term,
 29       lead(term) over (partition by person_id order by term) next_term,
 30       row_number() over (partition by person_id order by term) rn
 31     from test
 32    )
 33  select person_id,
 34    max(case when rn = 1 and term = 201030 then
 35               case when next_term in (201040, 201110) then next_term
 36                    else term
 37               end
 38             when rn = 1 and term in (201040, 201110) then term
 39        end) result
 40  from rns
 41  group by person_id;

 PERSON_ID     RESULT
---------- ----------
     14702     201030
     32356
     33634     201040
     14627     201110
     28103     201030
     53303     201040

6 rows selected.

SQL>

標籤：甲骨文 窗函数

上一篇：將sybase中的查詢更新遷移到oracle

下一篇：對照0檢查OracleSQLDECODE函式結果