我有一個大的 Oracle SQL 表，我需要一個遞增的 id 為每個 sequenceId 計數。但是如果會話號相同，它具有相同的id！我認為舉個例子更容易理解：
現在的情況：
| 時間戳 | 專案 ID | 個人身份 | 會議 | 序列號 |
|---|---|---|---|---|
| 2020-01-14 16:15:21 UTC | 5865526 | 465496 | 51c95e97-ffd6 | 1 |
| 2020-01-14 16:25:35 UTC | 5769989 | 465496 | 61c95e97-ffd6 | 1 |
| 2020-01-14 16:27:31 UTC | 5865524 | 465496 | 61c95e97-ffd6 | 1 |
| 2020-01-14 18:27:31 UTC | 5865524 | 465496 | 71c95e97-ffd6 | 1 |
| 2019-11-04 15:28:57 UTC | 5240 | 1120748 | 31c95e97-ffd6 | 2 |
| 2019-12-28 14:32:56 UTC | 5881337 | 1180452 | 41c95e97-ffd6 | 3 |
需要：
| 時間戳 | 專案 ID | 個人身份 | 會議 | 序列號 | 事件 ID |
|---|---|---|---|---|---|
| 2020-01-14 16:15:21 UTC | 5865526 | 465496 | 51c95e97-ffd6 | 1 | 1 |
| 2020-01-14 16:25:35 UTC | 5769989 | 465496 | 61c95e97-ffd6 | 1 | 2 |
| 2020-01-14 16:27:31 UTC | 5865524 | 465496 | 61c95e97-ffd6 | 1 | 2 |
| 2020-01-14 18:27:31 UTC | 5865524 | 465496 | 71c95e97-ffd6 | 1 | 3 |
| 2019-11-04 15:28:57 UTC | 5240 | 1120748 | 31c95e97-ffd6 | 2 | 1 |
| 2019-12-28 14:32:56 UTC | 5881337 | 1180452 | 41c95e97-ffd6 | 3 | 1 |
uj5u.com熱心網友回復：
像這樣的東西？
SQL> with t as (
2 select 'a' sessionid, 1 sequenceid from dual union all
3 select 'b' sessionid, 1 sequenceid from dual union all
4 select 'b' sessionid, 1 sequenceid from dual union all
5 select 'c' sessionid, 1 sequenceid from dual union all
6 select 'e' sessionid, 2 sequenceid from dual union all
7 select 'e' sessionid, 2 sequenceid from dual union all
8 select 'g' sessionid, 2 sequenceid from dual union all
9 select 'h' sessionid, 3 sequenceid from dual union all
10 select 'i' sessionid, 3 sequenceid from dual union all
11 select 'j' sessionid, 3 sequenceid from dual union all
12 select 'k' sessionid, 3 sequenceid from dual
13 )
14 select
15 t.*,
16 dense_rank() over ( partition by sequenceid order by sessionid ) as s
17 from t;
S SEQUENCEID S
- ---------- ----------
a 1 1
b 1 2
b 1 2
c 1 3
e 2 1
e 2 1
g 2 2
h 3 1
i 3 2
j 3 3
k 3 4
11 rows selected.
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/426882.html
標籤：sql 甲骨文 oracle-sqldeveloper